Vlookup partial text from a single cell

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I use a spread sheet which includes the customer address (city and state) in
a single (merged) cell. The spreadsheet also containes a look up table with
2-letter state abbreveations. I need to capture only the state from the
address (cell E5) for the vlookup.
=IF(VLOOKUP(E5,$GC$9:$GD$59,2,FALSE)=0,"NO",IF(VLOOKUP(E5,$GC$9:$GD$59,2,FALSE)>0,"Yes")) Excel 2003 SP1
 
Are you saying the you want to use "California" in the address cell to return
"CA"?

How are the City/State delimited in the address cell?
 
Are you saying the you want to use "California" in the address cell to return
"CA"?

How are the City/State delimited in the address cell?
 
If the state code is the last two letters in cell E5:

VLOOKUP(RIGHT(E5,2),$GC$9:$GD$59,2,FALSE)
 
If the state code is the last two letters in cell E5:

VLOOKUP(RIGHT(E5,2),$GC$9:$GD$59,2,FALSE)
 
Thank you Deb, this does exactly what I wanted.

Debra Dalgleish said:
If the state code is the last two letters in cell E5:

VLOOKUP(RIGHT(E5,2),$GC$9:$GD$59,2,FALSE)
Click to expand...
 
Thank you Deb, this does exactly what I wanted.

Debra Dalgleish said:
If the state code is the last two letters in cell E5:

VLOOKUP(RIGHT(E5,2),$GC$9:$GD$59,2,FALSE)
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Need VLOOKUP to Work Two Ways 0
Vlookup - need the array to be constant 2
Searching the formula (not the value) for a text string 1
Vlookup help on pulling more that one value. Any help is appreciated. thanks in advance 5
Averaging values from a list, based on user input 3
VLOOKUP return cell location and hyperlink 0
VLOOKUP formula 3
Using VLOOKUP with partial search values 6

Back
Top