Lookup up state abbreviations

I have a spreadsheet that is used for sales territories. In column A, I have customer names, in column B, I have the State abbrev., In column C I would like to return as salesperson's name based on the State.

Aco IL John Smith
ABco OH Jane Doe
Cco WI John Smith
Czco IL John Smith
Dco WY Adam Scott
Fco UT Adam Scott
FDco CT Bill Jones
etc WI John Smith

So I need a formula to enter in column C, that will return the correct salesperson based on the state in column B. (when auto filled down)

For example: IL,WI,MN,IA,ND,SD = John Smith
OH,PA,KY,MI, = Jane Doe
etc

Thanks!

If you create a list in the sheet with 2 columns... one containing the state abbreviations and the next containing the associated names then you can use Vlookup()

e.g

if in X1 you have:

IL,WI,MN,IA,ND,SD

and in Y1 you have:

John Smith

and in X2 you have:

OH,PA,KY,MI

and in Y2 you have:

Jane Doe... etc..

then use

=VLOOKUP("*"&B2&"*",$X$1:$Y$100,2,0)

where B2 contains State to lookup in your data and X1:Y100 contains the table described above...

then copy down

Well there is a way you can do that if you are in XL2007... it would be a long and messy formula and difficult to update if needed in the future

you can use LOOKUP() and list all 50 states with the associated people...



e.g.

=Lookup(B2,{"AL","AK","AR","AZ","CA","CO", etc.},{"John Smith","John Smith","Jane Doe","John Smith","John Smith","Jane Doe", etc...})

Note: States have to be listed in Alphabetic order and in quotes and associated names have to be corresponding to order of states....

It should work exactly the same... are you getting error?

That error is returned if a match is not found.. so make sure actual state abbreviation exists in your side table and it is correct and that there are no extra spaces in the B2 reference..

If you still have problems, post the workbook of likeness thereof.