Vlookup Drop Down List Multiple Condition

andersen_yunan

New Member
Joined
Feb 7, 2018
Messages
36
Hi, so I got this raw data

NameClassHobbies
AndrewAppleSwimming
RickyAppleReading
EdwinOrangeTennis
JamesPearReading

<colgroup><col span="2"><col></colgroup><tbody>
</tbody>

and I want to make drop down list with multiple condition (in this case, class and hobbies will be the dropdown) that will result in specific name, like this

ClassApple
HobbiesReading
Name
Ricky
-
-
-

<colgroup><col width="64" style="width:48pt" span="2"> </colgroup><tbody>
</tbody>

Is there any excel formula that I could use to get this result?
Any help would be appreciated. Thanks in advance!
 

Some videos you may like

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

dave2018

Board Regular
Joined
Mar 20, 2018
Messages
222
Excel 2013/2016
ABC
1NameClassHobbies
2AndrewAppleSwimming
3RickyAppleReading
4EdwinOrangeTennis
5JamesPearReading
6
7Class:Apple
8Hobbies:Reading
9
10Ricky
11
12
13
14
15

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet6

Array Formulas
CellFormula
A10{=IFERROR(INDEX($A$2:$A$5,SMALL(IF(IF($B$2:$B$5=$B$7,ROW(B2:B5)-ROW(B2)+1,FALSE)*IF($C$2:$C$5=$B$8,1,FALSE)=0,FALSE,IF($B$2:$B$5=$B$7,ROW(B2:B5)-ROW(B2)+1,FALSE)*IF($C$2:$C$5=$B$8,1,FALSE)),ROWS($A$10:A10))),"")}

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

You can drag A10 down as many matches as you think there might ever be (ie. 5 rows down).

Pls reply if you have any further questions
 

dave2018

Board Regular
Joined
Mar 20, 2018
Messages
222
Or, you can also do this one:

=IFERROR(INDEX($A$2:$A$5,SMALL(IF(MMULT(--(TRANSPOSE($B$7:$B$8)=$B$2:$C$5),{1;1})=2,ROW($B$2:$B$5)-ROW($B$2)+1),ROWS($A$10:A10))),"")

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

andersen_yunan

New Member
Joined
Feb 7, 2018
Messages
36
Hi Dave
Thank you so much it works perfectly!

Is this formula also can be used for multiple criteria? say there are more than three or more condition that I want to use
 

andersen_yunan

New Member
Joined
Feb 7, 2018
Messages
36
Sure, let's just say that with my current example I want to add another condition, like Surname
I want to know how the formula works if you don't mind :)

Thanks!
 

Watch MrExcel Video

Forum statistics

Threads
1,099,113
Messages
5,466,749
Members
406,495
Latest member
Arlind Elezi

This Week's Hot Topics

Top