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

 

[kweaver]

In your sample data there is no reference to laptops. How would that be determined?
Also, in your sample, Table 3 in Week 4: there is no entry for Table 3, so how do you determine it's "Nokia"?

 

[kweaver]

BTW: your post says 2019 but your profile says 2021...which is it? Makes a big difference.

 

[Sochen]

In your sample data there is no reference to laptops. How would that be determined?
Also, in your sample, Table 3 in Week 4: there is no entry for Table 3, so how do you determine it's "Nokia"?

Hello Sir,

Thank you for your response.
The left table which is the plan that set up in advance by weekly. And the right table of each week performance, which I also want to know the "Location" of each department, categories by week.

I can add on the result of department for laptop as below.

And I put excel 2019 because I want to formula also can be worked in office 2019 because some of the formulas in office 21 are not available in office 19.

Thank you!

Test 18.xlsx
	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								1	Table 2	Laptop	HP
20								1	Table 1	Laptop	Dell
21								1	Table 3	Laptop	ACER
22								1	Table 1	Laptop	Dell
23								2	Table 1	Laptop	HP
24								2	Table 1	Laptop	HP
25								2	Table 3	Laptop	ACER
26								2	Table 2	Laptop	Dell
27
Sheet1

 

[kweaver]

Your right table is still incomplete compared to the left table results. But, maybe this will work.
You'll have to change the ROW reference based on how any rows are in the right table or use a formula to count them.

My first suggestion:

021623 Misc.xlsx
	M	N	O	P	Q
9	Smart Phone
10	Table 1	iPhone	Vivo	LG	Samsung
11	Table 2	Nokia	iPhone		iPhone
12	Table 3	Samsung	Samsung	iPhone	Nokia
13	Laptop
14	Table 1	Dell	HP
15	Table 2	HP
16	Table 3	Acer
Sheet17

Cell Formulas
Range	Formula
N10:Q12	N10	=IF(0<>SUM(($B5 =$I$5:$I$24)*(C$3=$H$5:$H$24)*($B$4=$J$5:$J$24)),INDEX($K$5:$K$24,MAX(($B5=$I$5:$I$24)*(C$3=$H$5:$H$24)*($B$4=$J$5:$J$24)*ROW($1:$20))),"")
N14:Q16	N14	=IF(0<>SUM(($B9 =$I$5:$I$24)*(C$3=$H$5:$H$24)*($B$8=$J$5:$J$24)),INDEX($K$5:$K$24,MAX(($B9=$I$5:$I$24)*(C$3=$H$5:$H$24)*($B$8=$J$5:$J$24)*ROW($1:$20))),"")

 

[Sochen]

Your right table is still incomplete compared to the left table results. But, maybe this will work.
You'll have to change the ROW reference based on how any rows are in the right table or use a formula to count them.

My first suggestion:

021623 Misc.xlsx
	M	N	O	P	Q
9	Smart Phone
10	Table 1	iPhone	Vivo	LG	Samsung
11	Table 2	Nokia	iPhone		iPhone
12	Table 3	Samsung	Samsung	iPhone	Nokia
13	Laptop
14	Table 1	Dell	HP
15	Table 2	HP
16	Table 3	Acer
Sheet17

Cell Formulas
Range	Formula
N10:Q12	N10	=IF(0<>SUM(($B5 =$I$5:$I$24)*(C$3=$H$5:$H$24)*($B$4=$J$5:$J$24)),INDEX($K$5:$K$24,MAX(($B5=$I$5:$I$24)*(C$3=$H$5:$H$24)*($B$4=$J$5:$J$24)*ROW($1:$20))),"")
N14:Q16	N14	=IF(0<>SUM(($B9 =$I$5:$I$24)*(C$3=$H$5:$H$24)*($B$8=$J$5:$J$24)),INDEX($K$5:$K$24,MAX(($B9=$I$5:$I$24)*(C$3=$H$5:$H$24)*($B$8=$J$5:$J$24)*ROW($1:$20))),"")

My sincere apologies as I think my explanation is not clear though.
So please find the below explanation in the table below:

Test 18.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1	This is the table that created in advance by weekly							This is the table B which need to lookup the location from "Table A".
2	Table A		Week	Week	Week	Week
3	Department	Location	1	2	3	4		Criteria 1	Lookup the location?	Criteria 2	Criteria 3
4	Smart Phone	Table 1	iPhone	Vivo	LG	Samsung		Week	Location	Department	Categories
5	Smart Phone	Table 2	Nokia	iPhone	Samsung	iPhone		1	?	Smart Phone	iPhone
6	Smart Phone	Table 3	Samsung	Samsung	iPhone	Nokia		1	?	Smart Phone	iPhone
7	Laptop	Table 1	Dell	HP	ASUS	Dell		1	?	Smart Phone	Samsung
8	Laptop	Table 2	HP	Dell	HP	HP		1	?	Smart Phone	Nokia
9	Laptop	Table 3	ACER	ACER	Dell	ACER		2	?	Smart Phone	iPhone
10								2	?	Smart Phone	Vivo
11								2	?	Smart Phone	Samsung
12								2	?	Smart Phone	Vivo
13								3	?	Smart Phone	LG
14								3	?	Smart Phone	iPhone
15								4	?	Smart Phone	iPhone
16								4	?	Smart Phone	iPhone
17								4	?	Smart Phone	Samsung
18								4	?	Smart Phone	iPhone
19								1	?	Laptop	HP
20								1	?	Laptop	Dell
21								1	?	Laptop	ACER
22								1	?	Laptop	Dell
23								2	?	Laptop	HP
24								2	?	Laptop	HP
25								2	?	Laptop	ACER
26								2	?	Laptop	Dell
27
Sheet1 (2)

 

[Alex Blakenburg]

On the latest layout in post #6 you could use something like the below.
Note: If you now that the item is only ever going to be in one department you could simplify the formula quite a bit.

Book2
	A	B	C	D	E	F	G	H	I	J	K
1	This is the table that created in advance by weekly							This is the table B which need to lookup the location from "Table A".
2	Table A		Week	Week	Week	Week
3	Department	Location	1	2	3	4		Criteria 1	Lookup the location?	Criteria 2	Criteria 3
4	Smart Phone	Table 1	iPhone	Vivo	LG	Samsung		Week	Location	Department	Categories
5	Smart Phone	Table 2	Nokia	iPhone	Samsung	iPhone		1	Table 1	Smart Phone	iPhone
6	Smart Phone	Table 3	Samsung	Samsung	iPhone	Nokia		1	Table 1	Smart Phone	iPhone
7	Laptop	Table 1	Dell	HP	ASUS	Dell		1	Table 3	Smart Phone	Samsung
8	Laptop	Table 2	HP	Dell	HP	HP		1	Table 2	Smart Phone	Nokia
9	Laptop	Table 3	ACER	ACER	Dell	ACER		2	Table 2	Smart Phone	iPhone
10								2	Table 1	Smart Phone	Vivo
11								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								1	Table 2	Laptop	HP
20								1	Table 1	Laptop	Dell
21								1	Table 3	Laptop	ACER
22								1	Table 1	Laptop	Dell
23								2	Table 1	Laptop	HP
24								2	Table 1	Laptop	HP
25								2	Table 3	Laptop	ACER
26								2	Table 2	Laptop	Dell
Data

Cell Formulas
Range	Formula
I5:I26	I5	=LOOKUP(2, 1/( ($A$4:$A$9=J5)* (INDEX($A$4:$F$9, 0, MATCH(H5,$A$3:$F$3,0)) = K5 ) ), $B$4:$B$9)

 

[Sochen]

On the latest layout in post #6 you could use something like the below.
Note: If you now that the item is only ever going to be in one department you could simplify the formula quite a bit.

Book2
	A	B	C	D	E	F	G	H	I	J	K
1	This is the table that created in advance by weekly							This is the table B which need to lookup the location from "Table A".
2	Table A		Week	Week	Week	Week
3	Department	Location	1	2	3	4		Criteria 1	Lookup the location?	Criteria 2	Criteria 3
4	Smart Phone	Table 1	iPhone	Vivo	LG	Samsung		Week	Location	Department	Categories
5	Smart Phone	Table 2	Nokia	iPhone	Samsung	iPhone		1	Table 1	Smart Phone	iPhone
6	Smart Phone	Table 3	Samsung	Samsung	iPhone	Nokia		1	Table 1	Smart Phone	iPhone
7	Laptop	Table 1	Dell	HP	ASUS	Dell		1	Table 3	Smart Phone	Samsung
8	Laptop	Table 2	HP	Dell	HP	HP		1	Table 2	Smart Phone	Nokia
9	Laptop	Table 3	ACER	ACER	Dell	ACER		2	Table 2	Smart Phone	iPhone
10								2	Table 1	Smart Phone	Vivo
11								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								1	Table 2	Laptop	HP
20								1	Table 1	Laptop	Dell
21								1	Table 3	Laptop	ACER
22								1	Table 1	Laptop	Dell
23								2	Table 1	Laptop	HP
24								2	Table 1	Laptop	HP
25								2	Table 3	Laptop	ACER
26								2	Table 2	Laptop	Dell
Data

Cell Formulas
Range	Formula
I5:I26	I5	=LOOKUP(2, 1/( ($A$4:$A$9=J5)* (INDEX($A$4:$F$9, 0, MATCH(H5,$A$3:$F$3,0)) = K5 ) ), $B$4:$B$9)

Hello Sir,

It works, however, if I change put the duplicate text (iPhone in Week 1 in Yellow color), then the result of the location didn't capture the first match which is "Table 1"but it shows "Table 2 instead. So may I ask how to show result for the first match?

Please check the below table for the yellow color that I put duplicate text.
Thank you!

Test 18.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1	This is the table that created in advance by weekly							This is the table B which need to lookup the location from "Table A".
2	Table A		Week	Week	Week	Week
3	Department	Location	1	2	3	4		Criteria 1	Lookup the location?	Criteria 2	Criteria 3
4	Smart Phone	Table 1	iPhone	Vivo	LG	Samsung		Week	Location	Department	Categories
5	Smart Phone	Table 2	iPhone	iPhone	Samsung	iPhone		1	Table 2	Smart Phone	iPhone	I want the first match is Table1 as Table one is on the top
6	Smart Phone	Table 3	Nokia	Samsung	iPhone	Nokia		1	Table 3	Smart Phone	Nokia
7	Laptop	Table 1	Dell	HP	ASUS	Dell		1	Table 3	Smart Phone	Nokia
8	Laptop	Table 2	HP	Dell	HP	HP		2	Table 2	Smart Phone	iPhone
9	Laptop	Table 3	ACER	ACER	Dell	ACER		2	Table 1	Smart Phone	Vivo
10								2	Table 3	Smart Phone	Samsung
11								2	Table 1	Smart Phone	Vivo
12								3	Table 1	Smart Phone	LG
13								3	Table 3	Smart Phone	iPhone
14								4	Table 2	Smart Phone	iPhone
15								4	Table 2	Smart Phone	iPhone
16								4	Table 1	Smart Phone	Samsung
17								4	Table 2	Smart Phone	iPhone
18								1	Table 2	Laptop	HP
19								1	Table 1	Laptop	Dell
20								1	Table 3	Laptop	ACER
21								1	Table 1	Laptop	Dell
22								2	Table 1	Laptop	HP
23								2	Table 1	Laptop	HP
24								2	Table 3	Laptop	ACER
25								2	Table 2	Laptop	Dell
26
Sheet1 (2)

Cell Formulas
Range	Formula
I5:I25	I5	=LOOKUP(2,1/(($A$4:$A$9=J5)*(INDEX($A$4:$F$9,0,MATCH(H5,$A$3:$F$3,0))=K5)),$B$4:$B$9)

 

[Alex Blakenburg]

It would be so much easier if you were able to use XLookup or Filter.

Try this:

=IFERROR(INDEX($B$4:$B$9,AGGREGATE(15,6,(ROW($A$4:$A$9)-ROW($A$4)+1)/((INDEX($A$4:$F$9,0,MATCH(H5,$A$3:$F$3,0))=K5)*($A$4:$A$9=J5)),1)),"")

 

[Sochen]

It would be so much easier if you were able to use XLookup or Filter.

Try this:

=IFERROR(INDEX($B$4:$B$9,AGGREGATE(15,6,(ROW($A$4:$A$9)-ROW($A$4)+1)/((INDEX($A$4:$F$9,0,MATCH(H5,$A$3:$F$3,0))=K5)*($A$4:$A$9=J5)),1)),"")

Hello Sir,

I would highly appreciate it if you could also share about the xlookup.
Thank you!