# Multi Column vLookup

#### Richard2542

##### Board Regular
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

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

#### 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

##### Board Regular
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

##### Board Regular
Steve...perfect solution without using an array...thanks so much

#### Richard2542

##### Board Regular

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

##### Board Regular
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,106,341
Messages
5,510,745
Members
408,809
Latest member
Matthiasek

### This Week's Hot Topics

• Turn fraction around
Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
• TIme Clock record reformatting to ???
Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
• TextBox Match
hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
• Using Large function based on Multiple Criteria
Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
• Can you check my code please
Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
• Combining 2 pivot tables into 1 chart
Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...