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.
Click to expand...

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.
Click to expand...

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

Similar Threads

VBA-Table Word 2019 - Move a selected text, from a cell to the adjacent cell to its right, by pressing the ENTER key 0
concatenate entries from a column into a cell by logical AND withanother column 2
Word VBA - Automatic change content of a cell in table using dropdown list 5
Return a value from one column using an array and a single row 1
Comparing two columns and returning value from another column 1
Need to count no. of rows where cell in column A = "value1" AND cellin column B (same row) = "value2 2
Finding value using greater than, Vlookup, and nested If 2
Returning A Row & Column from a Table array 0

Top