Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2007 Natural language formula | Excel Worksheet Functions | |||
What happened to natural language in excel 2007? | Excel Discussion (Misc queries) | |||
Named Ranges & Natural Language Formuals | Excel Discussion (Misc queries) | |||
=bahttext(A1) please tell me about language in english how can i | Excel Worksheet Functions | |||
=bahttext(A1) please tell me about language in english how can i | Excel Worksheet Functions |