Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Return value from a list
It must be Monday. My brain isn't working this morning.
I have a list of states and the region that they are in. When I have a customer, I want to simply type in the state and it will automatically bring up their correct region. Currently, my region data is by row: PACIFIC WEST AZ CA HI NM NV EAST CT DE FL GA Can you help me come up with the correct value that will return "Pacific West" when the value of CA is entered? Thank you, Peanut |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Return value from a list
Your data isn't really structured for an efficient lookkup.
List it this way: AZ____Pacific West CA____Pacific West HI____Pacific West NM____Pacific West NV____Pacific West etc Assuming that list in on Sheet2, A1:B26 This formula returns the region for New Mexico =VLOOKUP("NM",Sheet2!A1:B26,2,0) Is that something you can work with? -------------------------- Regards, Ron (XL2003, Win XP) Microsoft MVP (Excel) "Peanut" wrote in message ... It must be Monday. My brain isn't working this morning. I have a list of states and the region that they are in. When I have a customer, I want to simply type in the state and it will automatically bring up their correct region. Currently, my region data is by row: PACIFIC WEST AZ CA HI NM NV EAST CT DE FL GA Can you help me come up with the correct value that will return "Pacific West" when the value of CA is entered? Thank you, Peanut |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Return value from a list
Nel ,
Peanut ha scritto: It must be Monday. My brain isn't working this morning. I have a list of states and the region that they are in. When I have a customer, I want to simply type in the state and it will automatically bring up their correct region. Currently, my region data is by row: PACIFIC WEST AZ CA HI NM NV EAST CT DE FL GA Can you help me come up with the correct value that will return "Pacific West" when the value of CA is entered? Thank you, Peanut Hi Peanut, you have to build up a lookup table such this: AZ PACIFIC WEST CA PACIFIC WEST HI PACIFIC WEST NM PACIFIC WEST NV PACIFIC WEST CT EAST DE EAST FL EAST GA EAST and then use the VLOOKUP formula to have the region. So if you insert the state in C2 and your lookup table is in A1:B9, the formula that give you the region is: =VLOOKUP(C2;$A1:$B$9;2;0) -- (I'm not sure of names of menus, options and commands, because translating from the Italian version of Excel...) Hope I helped you. Thanks in advance for your feedback. Ciao Franz Verga from Italy |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Return value from a list
That should fix it. Thanks for your help,
Peanut "Ron Coderre" wrote: Your data isn't really structured for an efficient lookkup. List it this way: AZ____Pacific West CA____Pacific West HI____Pacific West NM____Pacific West NV____Pacific West etc Assuming that list in on Sheet2, A1:B26 This formula returns the region for New Mexico =VLOOKUP("NM",Sheet2!A1:B26,2,0) Is that something you can work with? -------------------------- Regards, Ron (XL2003, Win XP) Microsoft MVP (Excel) "Peanut" wrote in message ... It must be Monday. My brain isn't working this morning. I have a list of states and the region that they are in. When I have a customer, I want to simply type in the state and it will automatically bring up their correct region. Currently, my region data is by row: PACIFIC WEST AZ CA HI NM NV EAST CT DE FL GA Can you help me come up with the correct value that will return "Pacific West" when the value of CA is entered? Thank you, Peanut |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Return value from a list
Hi Peanut,
Ready for VBA? I have data set out like yours and this function gives the Area: Function Where(State, Areas, List) State = UCase(State) cCount = List.Rows.Count rCount = List.Columns.Count For MyCol = 1 To rCount For myRow = 1 To cCount If State = List(myRow, MyCol) Then Where = Areas(myRow) Exit Function End If Next myRow Next MyCol Where = "Not found" End Function Call it with: =WHERE(Cell_with_single_state, range_with_areas, range_with abbreviations) Need help with VBA? See David McRitchie's site on "getting started" with VBA http://www.mvps.org/dmcritchie/excel/getstarted.htm best wishes -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email "Peanut" wrote in message ... It must be Monday. My brain isn't working this morning. I have a list of states and the region that they are in. When I have a customer, I want to simply type in the state and it will automatically bring up their correct region. Currently, my region data is by row: PACIFIC WEST AZ CA HI NM NV EAST CT DE FL GA Can you help me come up with the correct value that will return "Pacific West" when the value of CA is entered? Thank you, Peanut |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Return a list dependent upon the selection of a preceeding list | Excel Worksheet Functions | |||
Compare Value in Cell 1 to a List, Return Value if Match otherwise Return Null | Excel Discussion (Misc queries) | |||
return a list | Excel Worksheet Functions | |||
return a list | Excel Worksheet Functions | |||
look up a list and return a value | Excel Worksheet Functions |