Multi Column vLookup

Richard2542

New Member
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.

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

Eric W

MrExcel MVP
Try:

DEFGHIJKLM
1Name1Name2Name3Name4DateNameDate
2abcd1-Jank1-Feb1-Feb
3efgh2-Jan
4ijkl1-Feb
5mnop1-Mar

</tbody>
Sheet9

Array Formulas
CellFormula
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))}

</tbody>
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

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

steve the fish

Well-known Member
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))

Richard2542

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

Eric W

MrExcel MVP
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))

Richard2542

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

Richard2542

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

Alex89

New Member
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!

Richard2542

New Member
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.

1,101,988
Messages
5,484,049
Members
407,426
Latest member
Owen Chia

This Week's Hot Topics

• Finding issue in If elseif else with For each Loop
Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
• MsgBox Error
Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
• CELL FORMAT - IF CONDITION
My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
• Show numbers nearly the same
Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...