Excel - Formula with natural English language

Asked By adman on 21-Jan-09 07:55 PM
I've been having the hardest time figuring this out. Maybe someone here could

A column in my table has a series of city names. I have another table on a
different worksheet that allocates each of these cities to an area e.g.
Petaluma (city) = Northern Marin (area). I want to write a formula that
converts the city name on each row to the appropriate area.

I'm guessing this could be done with an IF and OR formula, but it would
probably be better if I could write that formula to look up my city/area
allocation on the other worksheet.

Remember: I'm dealing with names, not numbers. Is all of this even possible?
If so, how?

Thanks for your help.

demechani replied on 21-Jan-09 08:11 PM
Easy with vlookup

Assume your "city/area allocation" table is in Sheet2's cols A & B
In any other sheet,
City values, eg Petaluna are assumed in A2 down
In B2: =IF(A2="","",VLOOKUP(A2,Sheet2!A:B,2,0))
Copy down to return corresponding Area values.
Downloads:22,500 Files:370 Subscribers:66
Gord Dibben replied on 21-Jan-09 08:13 PM
Possible through a VLOOKUP function.

Sheet 2 has a two column table of cities and areas.......columns A and B

Sheet 1 has a one column table of cities.

In Sheet1 B1 enter   =VLOOKUP(A1,Sheet2!A:B,2,FALSE)

Double-click on B1 fill handle to copy down.

Gord Dibben  MS Excel MVP

On Wed, 21 Jan 2009 16:55:01 -0800, adman4
adman replied on 21-Jan-09 10:16 PM
Thanks guys. Those answers worked perfectly. Thanks so much for your help!