# Thread: Multi Column vLookup Thanks: 0 Likes: 0

1. ## Multi Column vLookup

I have a 5 column table - Column 1 thru Column 4 contain unique names; Column 5 contains a time value. I am attempting to use vLookup to search for a unique name that may appear in Column 1 thru Column 4 and return the time value in the adjacent Column 5. I have had no success in developing this formula and would appreciate some help.

2. ## Re: Multi Column vLookup

Try:

D E F G H I J K L M
1 Name1 Name2 Name3 Name4 Date Name Date
2 a b c d 1-Jan k 1-Feb 1-Feb
3 e f g h 2-Jan
4 i j k l 1-Feb
5 m n o p 1-Mar
Sheet9

Array Formulas
Cell Formula
L2 {=INDEX(Table1[Date],MATCH(1,(MMULT(--(Table1[[Name1]:[Name4]]=K2),ROW(Table1[Name1])^0)>0)+0,0))}
M2 {=INDEX(H2:H5,MATCH(1,(MMULT(--(D2:G5=K2),ROW(H2:H5)^0)>0)+0,0))}
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

The L2 formula is if you have an actual defined table, the M2 formula if you just use range addresses.

3. ## Re: Multi Column vLookup

Heres an option:

=INDEX(\$E\$2:\$E\$20,AGGREGATE(15,6,1/(\$A\$2:\$D\$20="LookupValue")*(ROW(\$A\$2:\$D\$20)-ROW(\$A\$2)+1),1))

4. ## Re: Multi Column vLookup

Eric...this is great, thanks...is there a way to create the above solution without using an array formula?

5. ## Re: Multi Column vLookup

steve the fish provided a non-array formula to do that. (Technically speaking, it still is an array formula under the covers, it just doesn't require the CSE when entering.)

If you want to use the table syntax, then this is how to convert that format:

=INDEX(Table1[Date],AGGREGATE(15,6,1/(Table1[[Name1]:[Name4]]=K2)*(ROW(Table1[[Name1]:[Name4]])-ROW(INDEX(Table1[Name1],1))+1),1))

6. ## Re: Multi Column vLookup

Steve...perfect solution without using an array...thanks so much

7. ## Re: Multi Column vLookup

Thanks so much...I just incorporated the formula into my workbook and it works GREAT!

8. ## Re: Multi Column vLookup

Hi,

The DGET formula is a great alternative to the vLookup function. The main difference is that it allows the user to use multiple criteria.

Here is a short video that explains it in more detail. Hope this helps!

9. ## Re: Multi Column vLookup

Alex89...thanks, I like this function (never used it before), unfortunately, I was unable to adopt it to solve my issue. I need to find a single value that could appear in any of the 1st 4 columns and then use the adjacent value in Column 5. Eric W and Steve the fish supplied me with a solution.