excel question regarding vlookup

You need to have the subdivision as a field in worksheet 1 with the addresses.

If A1 refers to the address you won't get anywhere.

A1 or whatever cell you would refer to would have to refer to the subdivision of the address.

It's a little hard to understand due to the formatting in your post, but does column A of the 1st worksheet contain the entire string, for example, "118 Silverstream"? Or just "Silverstream"? Obviously it needs to be the latter to work properly. If the cell contains the number and the subdivision, then you need to split the text into two columns or use text functions to isolate the subdivision name in the vlookup. Also, when you say you're not getting the desired result, is it an error? Or just the wrong county name?

Column A of 1st worksheet contains entire string such as "118 Silverstream" or "Lot 55 Port Royal" or "Port Royal 79". I would have just done text to columns seperated by space but the string is not consistent to get the subdivision seperated in a single column. When I said not getting desired result, I mean I'm getting both the wrong county and N/As.

sonofsam wrote:

I have 2 worksheets

1st Worksheet

Address field is the only imporant column

Examples: Lot 118 Silverstream

107 Fountainlake

120 Port Royale

2nd Worksheet name subdivision by county

Column 1 contains subdivisions (Port Royale)

Column 2 contains counties (Madison)

I am trying to do a vlookup on worksheet 1 that says if the address in this column contains the subdivision in worksheet 2, then return the county from worksheet 2.

Currently I have =vlookup(A1, 'subdision by county'!A:B, 2).I am not getting desired result. Any suggestions?

You'd need to have a column in worksheet 1 that is just the subdivision. VLookup is looking for an exact match, not a field that contains it within. I'm trying to think of a quick way for you to create a column of the subdivisions... the fact that some subdivisions are made of two words complicates this. You could always use the "Text to Columns" option, but this won't be perfect.

You could use =TRIM(RIGHT(A1,LEN(A1)-SEARCH(" ",A1,1))), but this would return "118 Silverstream" for your first example, which isn't right either. This equation should work for all that have the number at the beginning. Maybe you can do a search replace for "Lot " to "" if "Lot" is the only possible leading word?

Listen to the above. Rememember though that that the column which you are "matching" has to be sorted in alpha order ascending.

Ahh. Very good. Now, is there a way to find and replace all numbers with "". For example, I now have many cells like 169 Port Royale, 215 Silverstream, and I won't to get rid of the number. I can't do text to column here because I still have some fields where the subdivision is listed first (examples Silverstream 210, Royalton Woods, etc).

Just write a little macro to do this for one cell, then use it in a formula and copy it down.

how many subdivisions are there? if there's not a ton, then try this:

1. list the subdivisions across the top in your first sheet.

2. use the if, find, and iserror functions to see whether the subdivision is within the address

3. each address will then have only one number or all blanks

under the subdivision columns

4. have a separate column to get the max number from under the subdivision columns

5. use that max number and do a hlookup to get just the subdivision

6. now do the vlookup

sounds complicated but it's easy and quick to setup.

or send me an email and i will send you my sample sheet.

I'm learning a lot today. Thanks guys

I did not know about the iserror function! That's fantastic! Thank you!