If and Then Formula

[Renee Nguy]

This is what I currently have:
A1=09508375, then B1 should be San Diego because the account number starts
with 09
A2=18509068, then B2 should be Newport because the account number starts
with 18
A3=13500291, then B3 should be Portland because the account number starts
with 13
Please help me create a formula where it will pick up the first two digits
of the account number in column A and match it to the location and provide
the correct location in column B.
Thank you so much.
Renee

 

[Dave Peterson]

Start a new worksheet in the same workbook. I'll call it Sheet2

Put the numbers in column A and the cities in column B.

Then put this in B1 of sheet1:
=if(a1="","",if(iserror(vlookup(--left(a1,2),sheet2!a:b,2,false)),"Missing",
vlookup(--left(a1,2),sheet2!a:b,2,false)))

You may want to read Debra Dalgleish's notes:
http://www.contextures.com/xlFunctions02.html (for =vlookup())
and
http://www.contextures.com/xlFunctions03.html (for =index(match()))