Parse a column using a VLOOKUP Table and post a result to another cell/column

K

Kerry Facer

I have a column in of city codes joined with slashes, there may be 2 to 5 codes in a cell.
I need to be able to test or check for a combination against a table then post the result in another cell if the result exists.

Example, the column will contain the following data:

AMS/PHL/SFO

AMS/FRA/PHL/LAX

FCO/LHR/PHL


I have a table set up as follows:

AMS/PHL 1

FRA/PHL 1

LHR/PHL 1


I need the top grouping to check against the bottom table and return a value as a VLOOKUP would (1) or to return the actual value checked in the lower table.
 
R

Ron Rosenfeld

I have a column in of city codes joined with slashes, there may be 2 to 5 codes in a cell.
I need to be able to test or check for a combination against a table then post the result in another cell if the result exists.

Example, the column will contain the following data:

AMS/PHL/SFO

AMS/FRA/PHL/LAX

FCO/LHR/PHL


I have a table set up as follows:

AMS/PHL 1

FRA/PHL 1

LHR/PHL 1


I need the top grouping to check against the bottom table and return a value as a VLOOKUP would (1) or to return the actual value checked in the lower table.

Perhaps something as simple as:

=IFERROR(VLOOKUP(LEFT(A1,7),BottomTable,1,FALSE),"")&
IFERROR(VLOOKUP(MID(A1,5,7),BottomTable,1,FALSE),"")&
IFERROR(VLOOKUP(MID(A1,9,7),BottomTable,1,FALSE),"")&
IFERROR(VLOOKUP(MID(A1,13,7),BottomTable,1,FALSE),"")

depending on how your real data is set up.
 
K

Kerry Facer

I have a column in of city codes joined with slashes, there may be 2 to 5 codes in a cell.

I need to be able to test or check for a combination against a table then post the result in another cell if the result exists.



Example, the column will contain the following data:



AMS/PHL/SFO



AMS/FRA/PHL/LAX



FCO/LHR/PHL





I have a table set up as follows:



AMS/PHL 1



FRA/PHL 1



LHR/PHL 1





I need the top grouping to check against the bottom table and return a value as a VLOOKUP would (1) or to return the actual value checked in the lower table.

THANKS!!Worked,perfect!
 

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

Top