How to lookup text with multi criteria (in Excel 2019)

[Sochen]

Hi All,

How to lookup "location" in yellow highlight below with 3 criteria which include "Week", "Department", "Categories":

Book2
	A	B	C	D	E	F	G	H	I	J	K	L
1
2
3		Week	1	2	3	4		Criteria 1		Criteria 2	Criteria 3
4		Smart Phone						Week	Location	Department	Categories
5		Table 1	iPhone	Vivo	LG	Samsung		1	Table 1	Smart Phone	iPhone
6		Table 2	Nokia	iPhone	Samsung	iPhone		1	Table 1	Smart Phone	iPhone
7		Table 3	Samsung	Samsung	iPhone	Nokia		1	Table 3	Smart Phone	Samsung
8		Laptop						1	Table 2	Smart Phone	Nokia
9		Table 1	Dell	HP	ASUS	Dell		2	Table 2	Smart Phone	iPhone
10		Table 2	HP	Dell	HP	HP		2	Table 1	Smart Phone	Vivo
11		Table 3	ACER	ACER	Dell	ACER		2	Table 3	Smart Phone	Samsung
12								2	Table 1	Smart Phone	Vivo
13								3	Table 1	Smart Phone	LG
14								3	Table 3	Smart Phone	iPhone
15								4	Table 2	Smart Phone	iPhone
16								4	Table 2	Smart Phone	iPhone
17								4	Table 1	Smart Phone	Samsung
18								4	Table 2	Smart Phone	iPhone
19
Sheet1

 

[Alex Blakenburg]

Here is an XLookup version:

=XLOOKUP(1,
      ($A$4:$A$9=J5)*(XLOOKUP(H5,$A$3:$F$3,$A$4:$F$9)=K5),
       $B$4:$B$9,
       "")

Here is an XLookup with Index Match instead of a 2nd XLookup

=XLOOKUP(1,
      ($A$4:$A$9=J5)*(INDEX($A$4:$F$9,0,MATCH(H5,$A$3:$F$3,0))=K5),
       $B$4:$B$9,
        "" )

And the filter option:

=LET(fltrWk, FILTER( $C$4:$F$9, $C$3:$F$3=H5 ),
          FILTER( $B$4:$B$9, ($A$4:$A$9=J5) * (fltrWk=K5), "" ) )

 

[Sochen]

Here is an XLookup version:

=XLOOKUP(1,
      ($A$4:$A$9=J5)*(XLOOKUP(H5,$A$3:$F$3,$A$4:$F$9)=K5),
       $B$4:$B$9,
       "")

Here is an XLookup with Index Match instead of a 2nd XLookup

=XLOOKUP(1,
      ($A$4:$A$9=J5)*(INDEX($A$4:$F$9,0,MATCH(H5,$A$3:$F$3,0))=K5),
       $B$4:$B$9,
        "" )

And the filter option:

=LET(fltrWk, FILTER( $C$4:$F$9, $C$3:$F$3=H5 ),
          FILTER( $B$4:$B$9, ($A$4:$A$9=J5) * (fltrWk=K5), "" ) )

Hello Sir,

It works perfectly.
Thank you so much for sharing.
Have a great day!