Index and Match with three conditions

[josvill2010]

Hi,
I have tried to add another condition to this index formula as I have noted sometime the number that I am looking for eg A purchase order (PO) number might appear twice under a different label (GL).
The PO might have two different values for the two different labels. The formula also bring the information of the month of when the PO was used.
For example
This formula is working
=INDEX('Open PO'!$A:$X,MATCH($C20,'Open PO'!$G:$G,0),MATCH(T$11,'Open PO'!$A$3:$X$3,0))
But then I need help with adding another criteria.

=INDEX('Open PO'!$A:$X,MATCH(1,($C20='Open PO'!$G:$G)*($C16='Open PO'!E:E),0),(T$11='Open PO'!$A$3:$X$3))
I want to add the criteria for the GL so it brings a different line but it is not working

Excel to test.xlsx
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
11		PO	Supplier	Title	Description	Requestor	Need by	Amount	Billed	Unbilled	Run Rate	Q2'20	Q3'20	Q4'20	Q1'21	Q2'21	Q3'21	Q4'21	Q1'22
12
13
14	CLN	2010 - VEHICLES (LEASES & PURCHASES) - OPEX										-	11,494	23,782	5,366	4,531	4,396	5,079	3,335
15		PO	Supplier	Title	Description	Requestor	Need by	Amount	Billed	Unbilled	Run Rate
16	CLN	GL EQUIPMENT										-	-	-	-	-	-	45,999	35,075
17		PO	Supplier	Title	Description	Requestor	Need by	Amount	Billed	Unbilled	Run Rate
18		70000156486	test 1	test 2	test 3	test 4	test 5	test 6	test 7	test 8	test 9	-	-	-	-	-	-	-	-
19		70000059248	test 1	test 2	test 3	test 4	test 5	test 6	test 7	test 8	test 9	-	-	-	-	-	-	-	-
20		70000012329	test 1	test 2	test 3	test 4	test 5	test 6	test 7	test 8	test 9	-	-	-	4,397	4,531	4,396	5,079	$3,335
21																			EMEA
CLN

Cell Formulas
Range	Formula
T20	T20	=INDEX('Open PO'!$A:$X,MATCH($C20,'Open PO'!$G:$G,0),MATCH(T$11,'Open PO'!$A$3:$X$3,0))
T21:AQ21	T21	=INDEX('Open PO'!$A:$X,MATCH(1,($C20='Open PO'!$G:$G)*($C16='Open PO'!E:E),0),(T$11='Open PO'!$A$3:$X$3))
Dynamic array formulas.

 

[KRice]

Could you provide a short example of the "Open PO" worksheet? And could you clarify something, please? While the PO number may appear multiple times, do you expect that it will appear only once for a particular label?

 

[josvill2010]

Excel to test.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X
1	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24
2	EMEA	ACTUALS By PO														16	2020	2020	2020	2021	2021	2021	2021	2022
3	Region	Site	Supplier	Level 0 DPC	Level 1	Requestor	PO #	Amount	Unbilled	Billed	Need By	Time %	Billed %	Title	Description	Run Rate	Q2'20	Q3'20	Q4'20	Q1'21	Q2'21	Q3'21	Q4'21	Q1'22
54	EMEA	TEST A	HEIGHT FOR HIRE LTD	MTRLS	GL TOOLS	Joe B	2013281	50000	1500	48500	2/28/2022	100%	97%	Test1	Test1	$0	$0	$0	$0	$0	$0	$0	$0	$0
55	EMEA	TEST A	HEIGHT FOR HIRE LTD	MTRLS	NO GL EQUIPMENT	Joe B	70000012329	10000	0	10000	2/28/2022	100%	100%	Test1	Test1	$0	$0	$0	$0	$0	$0	$0	$0	$3,335
56	EMEA	TEST A	HEIGHT FOR HIRE LTD	MTRLS	GL EQUIPMENT	Joe B	70000012329	15000	9000	6000	2/28/2022	100%	40%	Test2	Test2	$0	$0	$0	$0	$0	$0	$0	$0	$0
Open PO

Cell Formulas
Range	Formula
J54:J56	J54	=H54-I54
M54:M56	M54	=J54/H54

 

[josvill2010]

Copy of the formulas

Excel to test.xlsx
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
11	Company	PO	Supplier	Title	Description	Requestor	Need by	Amount	Billed	Unbilled	Run Rate	Q2'20	Q3'20	Q4'20	Q1'21	Q2'21	Q3'21	Q4'21	Q1'22
12
13
14	TEST A	NO GL EQUIPMENT										-	11,494	23,782	5,366	4,531	4,396	5,079	3,335
15		PO	Supplier	Title	Description	Requestor	Need by	Amount	Billed	Unbilled	Run Rate
16	TEST A	GL EQUIPMENT										-	-	-	-	-	-	45,999	35,075
17		PO	Supplier	Title	Description	Requestor	Need by	Amount	Billed	Unbilled	Run Rate
18		70000156486	test 1	test 2	test 3	test 4	test 5	test 6	test 7	test 8	test 9	-	-	-	-	-	-	-	-
19		70000059248	test 1	test 2	test 3	test 4	test 5	test 6	test 7	test 8	test 9	-	-	-	-	-	-	-	-
20		70000012329	test 1	test 2	test 3	test 4	test 5	test 6	test 7	test 8	test 9	-	-	-	4,397	4,531	4,396	5,079	$3,335
21																			EMEA
TEST A

Cell Formulas
Range	Formula
T20	T20	=INDEX('Open PO'!$A:$X,MATCH($C20,'Open PO'!$G:$G,0),MATCH(T$11,'Open PO'!$A$3:$X$3,0))
T21	T21	=INDEX('Open PO'!$A:$X,MATCH(1,($C20='Open PO'!$G:$G)*($C16='Open PO'!E:E),0),(T$11='Open PO'!$A$3:$X$3))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Fluff]

Try it like

=INDEX('Open PO'!$A:$X,MATCH(1,($C20='Open PO'!$G:$G)*($C16='Open PO'!E:E),0),MATCH(T$11,'Open PO'!$A$3:$X$3,0))

 

[josvill2010]

it worked without flaws.

Why do I need to use use match(1,..)
I tried to multiply both Match for rows, but it didnt work
This is the formula i tried by copying yours
=INDEX('Open PO'!$A:$X,(MATCH(C20,'Open PO'!G:G,0)*MATCH(C16,'Open PO'!E:E,0)),MATCH(T$11,'Open PO'!$A$3:$X$3,0))
Yours:
=INDEX('Open PO'!$A:$X,MATCH(1,($C20='Open PO'!$G:$G)*($C16='Open PO'!E:E),0),MATCH(T$11,'Open PO'!$A$3:$X$3,0))

 

[Fluff]

I just copied you T21 formula & change the column argument back to using match, as you had in T20