Reverse lookup with 3 Match conditions

[rex759]

Hello,
The formula pulls the header number correctly for the first “X” that appears in the columns (C:Q) if the Year in B12 matches in Column B.

I want to add another condition in column A, the Product ID to be included with matching the selected year. I tried just adding another Match function to the end but couldn’t get it to work. Any ideas?

Index Match, header reverser lookup.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1			1	2	3	4	5	6	7	8	9	10	11	12	13	14	15
2	19	2018									X	X	X	X	X	X	X
3	19	2019									X	X	X	X	X	X	X
4	19	2021									X	X	X	X	X	X	X
5	19	2022									X	X	X	X	X	X	X
6	19	2023					x	X	X	X	X	X	X	X
7	21	2023			x
8
9
10	x
11	Product	Year	Week
12	19	2023	5
13
Sheet1

Cell Formulas
Range	Formula
C12	C12	=INDEX($C$1:$Q$1,,MATCH(A10,INDEX(C2:Q7,MATCH(B12,B2:B7,0),),0))

 

[AhoyNC]

Try:

Book2
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1			1	2	3	4	5	6	7	8	9	10	11	12	13	14	15
2	19	2018									X	X	X	X	X	X	X
3	19	2019									X	X	X	X	X	X	X
4	19	2021									X	X	X	X	X	X	X
5	19	2022									X	X	X	X	X	X	X
6	19	2023					x	X	X	X	X	X	X	X
7	21	2023			x
8
9
10	x
11	Product	Year	Week
12	19	2023	5
Sheet1

Cell Formulas
Range	Formula
C12	C12	=INDEX($C$1:$Q$1,MATCH(A10,FILTER($C$2:$Q$7,($A$2:$A$7=A12)*($B$2:$B$7=B12)),0))

 

[rex759]

Wow, that Filter Function is pretty cool! Will take a deep look into its abilities. Thank you very much

 

[AhoyNC]

You're welcome. Thanks for the feedback.
The attached link on FILTER function may be of interest.

 

[rex759]

My one stop shopping place! Thanks again.