Vlookup partial text from a single cell
 

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(VLO OKUP(E5,$GC$9:$GD$59,2,FALSE)0,"Yes")) Excel 2003 SP1

Vlookup partial text from a single 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?

"Sparky13" wrote:

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(VLO OKUP(E5,$GC$9:$GD$59,2,FALSE)0,"Yes")) Excel 2003 SP1

Vlookup partial text from a single cell
 

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

VLOOKUP(RIGHT(E5,2),$GC$9:$GD$59,2,FALSE)

Sparky13 wrote:
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(VLO OKUP(E5,$GC$9:$GD$59,2,FALSE)0,"Yes")) Excel 2003 SP1


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

Vlookup partial text from a single cell
 

Thank you Deb, this does exactly what I wanted.

"Debra Dalgleish" wrote:

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

VLOOKUP(RIGHT(E5,2),$GC$9:$GD$59,2,FALSE)

Sparky13 wrote:
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(VLO OKUP(E5,$GC$9:$GD$59,2,FALSE)0,"Yes")) Excel 2003 SP1


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

Vlookup partial text from a single cell
 

You're welcome. Thanks for letting me know that it helped.

Sparky13 wrote:
Thank you Deb, this does exactly what I wanted.

"Debra Dalgleish" wrote:


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

VLOOKUP(RIGHT(E5,2),$GC$9:$GD$59,2,FALSE)

Sparky13 wrote:

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


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html





--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html