Vlookup Drop Down List Multiple Condition

[andersen_yunan]

Hi, so I got this raw data

[TABLE="width: 200"]
<colgroup><col span="2"><col></colgroup><tbody>[TR]
[TD]Name[/TD]
[TD]Class[/TD]
[TD]Hobbies[/TD]
[/TR]
[TR]
[TD]Andrew[/TD]
[TD]Apple[/TD]
[TD]Swimming[/TD]
[/TR]
[TR]
[TD]Ricky[/TD]
[TD]Apple[/TD]
[TD]Reading[/TD]
[/TR]
[TR]
[TD]Edwin[/TD]
[TD]Orange[/TD]
[TD]Tennis[/TD]
[/TR]
[TR]
[TD]James[/TD]
[TD]Pear[/TD]
[TD]Reading[/TD]
[/TR]
</tbody>[/TABLE]

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

[TABLE="width: 128"]
<colgroup><col width="64" style="width:48pt" span="2"> </colgroup><tbody>[TR]
[TD="width: 64, bgcolor: transparent"]Class[/TD]
[TD="width: 64, bgcolor: #FFFF00"]Apple[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Hobbies[/TD]
[TD="bgcolor: #FFFF00"]Reading[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Name[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Ricky[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]-[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]-[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]-[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]

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
Name	Class	Hobbies
Andrew	Apple	Swimming
Ricky	Apple	Reading
Edwin	Orange	Tennis
James	Pear	Reading
Class:	Apple
Hobbies:	Reading
Ricky

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]4[/TD]

[TD="align: center"]5[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet6​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]A10[/TH]
[TD="align: left"]{=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))),"")}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

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!