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?