Lookup Data With 3 Criterias

[muhammad susanto]

hi all...

how to solve this problem

Data Rapot khusus PENILAIAN NEW.xlsx
	A	B	C	D
1	Description	Names
2		Farah	Tika	Dyla
3	Mango	good	bad	worst
4	Orange	bad	good	worst
5	Banana	worst	worst	worst
6
7	Target/expected desired
8	Name ---->>	Tika
9
10	Mango	bad
11	Orange	good
12	Banana	worst
formulas

my data from cell A1 till D5
in cell B8 as reference name (dropdown name list)
my target/expected result start from B10 till B12

i hope someone would help me out..

.sst

 

[saboh12617]

Hello,

When copying your data in A1 (so that "mango" is in A3), the formula below returns the expected result (spilled range).

=INDEX($B$3:$D$5,{1,2,3},XMATCH($B$8,$B$2:$D$2))

 

[muhammad susanto]

hi ..

i'm sorry please use excel 2010 or 2013 formula
for this case

 

[saboh12617]

Yes sure, it's almost the same

=INDEX($B$3:$D$5,{1,2,3},MATCH($B$8,$B$2:$D$2,0))

Just select cells B10:B12 and enter it via CTRL+SHIFT+ENTER.

 

[muhammad susanto]

hi saboh12617, thank you so much.
working well.

 

[MARZIOTULLIO]

B10=IFERROR(INDEX($B$3:$D$5,MATCH($A10,$A$3:$A$5,0),MATCH($B$8,$B$2:$D$2,0)),"")

Copy dowm

 

[saboh12617]

Glad to help, have a good day.