Vlookup Drop Down List Multiple Condition

[andersen_yunan]

Hi, so I got this raw data

Name	Class	Hobbies
Andrew	Apple	Swimming
Ricky	Apple	Reading
Edwin	Orange	Tennis
James	Pear	Reading

<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

Class	Apple
Hobbies	Reading
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!

 

[dave2018]

Excel 2013/2016

	A	B	C
1	Name	Class	Hobbies
2	Andrew	Apple	Swimming
3	Ricky	Apple	Reading
4	Edwin	Orange	Tennis
5	James	Pear	Reading
6
7	Class:	Apple
8	Hobbies:	Reading
9
10	Ricky
11
12
13
14
15

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

Sheet6​

Array Formulas Cell Formula 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]

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

 

[andersen_yunan]

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

 

[dave2018]

abosulutely!

Do you want me to explain detailed? or do you want to post your new example?

 

[andersen_yunan]

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!