Return value from a list

[Peanut]

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

 

[Ron Coderre]

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)

 

[Franz Verga]

Nel post:[email protected],

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

 

[Peanut]

That should fix it. Thanks for your help,
Peanut

 

[Bernard Liengme]

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