Formula with natural English language
 

I've been having the hardest time figuring this out. Maybe someone here could
help?

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.

Formula with natural English language
 

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.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,500 Files:370 Subscribers:66
xdemechanik
---
"adman4" wrote:
I've been having the hardest time figuring this out. Maybe someone here could
help?

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.

Formula with natural English language
 

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
wrote:

I've been having the hardest time figuring this out. Maybe someone here could
help?

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.

Formula with natural English language
 

Thanks guys. Those answers worked perfectly. Thanks so much for your help!

"Gord Dibben" wrote:

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
wrote:

I've been having the hardest time figuring this out. Maybe someone here could
help?

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.