Multi Column vLookup

Richard2542

New Member
Joined
Apr 27, 2017
Messages
38
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.
 

Some videos you may like

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
9,910
Try:

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

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</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))}

<thead>
</thead><tbody>
</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
Joined
Oct 20, 2009
Messages
8,080
Office Version
365
Platform
Windows
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
Joined
Apr 27, 2017
Messages
38
Eric...this is great, thanks...is there a way to create the above solution without using an array formula?
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
9,910
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
Joined
Apr 27, 2017
Messages
38
Thanks so much...I just incorporated the formula into my workbook and it works GREAT!
 

Richard2542

New Member
Joined
Apr 27, 2017
Messages
38
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,102,436
Messages
5,486,879
Members
407,569
Latest member
Huzz

This Week's Hot Topics

Top