VLOOKUP For multiple columns of data

[dlove]

Here is my scenario.

I have 4 columns of part numbers that I need to cross check against a table, and then return the last column from the table if it finds a match.

I could just do 4 seperate VLOOKUPS for each column, but I'm wondering if there is formula that will allow the lookup value to be a1 first, if it doesn't find a match, make B1 the lookup value, if that doesn't find a match make C1 the lookup value and so on. In the end if nothing returns a match I will know that no part in the 4 columns was found in my data table. If it does find a match, it will return the column index I'm looking for from my data table.


Any help is appreciated.

 

[Taul]

Assuming you last column (the “return value”) is actually a value and not text.
Sheet set-up in the following layout
(I would suggest you set it up this way, and then modify it for the ranges you are using).

Table of data is K3 to N17 with headers above
The range A2 to D2 should be the same headers, ie. Part1, Par2, Par3, Par4, Return value.
The range A3 to D3 and below will be the part numbers you are searching for and in the cell E4 you can use the following formula

=SUMPRODUCT(--($K$3:$K$17=A3),--($L$3:$L$17=B3),--($M$3:$M$17=C3),--($N$3:$N$17=D3),--($O$3:$O$17))

If your return data is text, we will need to find an alternative approach.