Return value from a list

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Peanut - 17 Sep 2007 17:34 GMT

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 to this Message

Ron Coderre - 17 Sep 2007 17:39 GMT

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)

> It must be Monday. My brain isn't working this morning.
>
[quoted text clipped - 11 lines]
> Thank you,
> Peanut

Reply to this Message

Peanut - 17 Sep 2007 17:52 GMT

That should fix it. Thanks for your help,
Peanut

> Your data isn't really structured for an efficient lookkup.
>
[quoted text clipped - 34 lines]
> > Thank you,
> > Peanut

Reply to this Message

Franz Verga - 17 Sep 2007 17:45 GMT

Nel post:29E2B7C1-C272-47BD-A013-311D339E751D@microsoft.com,
Peanut <Peanut@discussions.microsoft.com> ha scritto:

> It must be Monday. My brain isn't working this morning.
>
[quoted text clipped - 11 lines]
> 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)

Signature

(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

Reply to this Message

Bernard Liengme - 17 Sep 2007 18:10 GMT

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

Signature

Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

> It must be Monday. My brain isn't working this morning.
>
[quoted text clipped - 11 lines]
> Thank you,
> Peanut

Reply to this Message