Index match with multiple criteria, sheets & columns

[Tbaileyco]

Hello All,

I am trying to get a Index Match formula to work on my sheet Currently I am trying to use the following but its not working. =INDEX(NewDemand!$A$1:$CA$55,MATCH($C$1,NewDemand!$A$1:$A$55,0),MATCH($E18,NewDemand!$A$1:$CA$1,0),MATCH($O$1,NewDemand!$Q$1:$Q$55,0))

First Sheet:

Demand Plan1.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1			1111												TotReq
2															PIRCon
3															CusReq
4															CusAck
5															SPLY
6															PAB
7
8			AVG Weekly Usage:			NOTES:
9
10
11
12
13
14
15
16			Start	End	Period C.Y.	On-Hand	Demand	Supply	Note
17			11/11/23	11/17/23	Prior	582	100	-
18			11/18/23	11/24/23	2023/47	482	#REF!	-	G18 - should pull in 560
19			11/25/23	12/01/23	2023/48	#REF!	#REF!	-
20			12/02/23	12/08/23	2023/49	#REF!	#REF!	-
21			12/09/23	12/15/23	2023/50	#REF!	#REF!	-	G21 - should pull in 280
22			12/16/23	12/22/23	2023/51	#REF!	#REF!	-
23			12/23/23	12/29/23	2023/52	#REF!	#REF!	-
24			12/30/23	01/05/24	2024/1	#REF!	#REF!	500
25			01/06/24	01/12/24	2024/2	#REF!	#REF!	-
26			01/13/24	01/19/24	2024/3	#REF!	#REF!	-
27			01/20/24	01/26/24	2024/4	#REF!	#REF!	-
28			01/27/24	02/02/24	2024/5	#REF!	#REF!	-
29			02/03/24	02/09/24	2024/6	#REF!	#REF!	-
30			02/10/24	02/16/24	2024/7	#REF!	#REF!	-
31			02/17/24	02/23/24	2024/8	#REF!	#REF!	-
32			02/24/24	03/01/24	2024/9	#REF!	#REF!	5,000
33			03/02/24	03/08/24	2024/10	#REF!	#REF!	-
34			03/09/24	03/15/24	2024/11	#REF!	#REF!	-
35			03/16/24	03/22/24	2024/12	#REF!	#REF!	-
36			03/23/24	03/29/24	2024/13	#REF!	#REF!	-
37			03/30/24	04/05/24	2024/14	#REF!	#REF!	-
38			04/06/24	04/12/24	2024/15	#REF!	#REF!	-
39			04/13/24	04/19/24	2024/16	#REF!	#REF!	-
40			04/20/24	04/26/24	2024/17	#REF!	#REF!	500
41			04/27/24	05/03/24	2024/18	#REF!	#REF!	-
42			05/04/24	05/10/24	2024/19	#REF!	#REF!	-
43			05/11/24	05/17/24	2024/20	#REF!	#REF!	-
44			05/18/24	05/24/24	2024/21	#REF!	#REF!	-
45			05/25/24	05/31/24	2024/22	#REF!	#REF!	-
46			06/01/24	06/07/24	2024/23	#REF!	#REF!	-
47			06/08/24	06/14/24	2024/24	#REF!	#REF!	-
48			06/15/24	06/21/24	2024/25	#REF!	#REF!	5,000
49			06/22/24	06/28/24	2024/26	#REF!	#REF!	-
50			06/29/24	07/05/24	2024/27	#REF!	#REF!	-
51			07/06/24	07/12/24	2024/28	#REF!	#REF!	-
52			07/13/24	07/19/24	2024/29	#REF!	#REF!	-
53			07/20/24	07/26/24	2024/30	#REF!	#REF!	-
54			07/27/24	08/02/24	2024/31	#REF!	#REF!	-
55			08/03/24	08/09/24	2024/32	#REF!	#REF!	-
56			08/10/24	08/16/24	2024/33	#REF!	#REF!	500
57			08/17/24	08/23/24	2024/34	#REF!	#REF!	-
58			08/24/24	08/30/24	2024/35	#REF!	#REF!	-
59			08/31/24	09/06/24	2024/36	#REF!	#REF!	-
60			09/07/24	09/13/24	2024/37	#REF!	#REF!	-
61			09/14/24	09/20/24	2024/38	#REF!	#REF!	-
62			09/21/24	09/27/24	2024/39	#REF!	#REF!	-
63			09/28/24	10/04/24	2024/40	#REF!	#REF!	-
64			10/05/24	10/11/24	2024/41	#REF!	#REF!	900
65			10/12/24	10/18/24	2024/42	#REF!	#REF!	-
66			10/19/24	10/25/24	2024/43	#REF!	#REF!	-
67			10/26/24	11/01/24	2024/44	#REF!	#REF!	-
68			11/02/24	11/08/24	2024/45	#REF!	#REF!	-
69			11/09/24	11/15/24	2024/46	#REF!	#REF!	-
70			11/16/24	11/22/24	2024/47	#REF!	#REF!	-
71			11/23/24	11/29/24	2024/48	#REF!	#N/A	-
72			11/30/24	12/06/24	2024/49	#REF!	#N/A	2,000
73			12/07/24	12/13/24	2024/50	#REF!	#N/A	-
74			12/14/24	12/20/24	2024/51	#REF!	#N/A	-
75			12/21/24	12/27/24	2024/52	#REF!	#N/A	-
DemandPlan

Cell Formulas
Range	Formula
F18:F75	F18	=F17-G17+H17
G18:G75	G18	=INDEX(NewDemand!$A$1:$CA$55,MATCH($C$1,NewDemand!$A$1:$A$55,0),MATCH($E18,NewDemand!$A$1:$CA$1,0),MATCH($O$1,NewDemand!$Q$1:$Q$55,0))

Named Ranges
Name	Refers To	Cells
NewDemand!_FilterDatabase	=NewDemand!$A$1:$CA$55	G18:G75

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
F17:F75	Cell Value	<0	text	NO
F17:F75	Cell Value	>1	text	NO

Second Sheet:

Demand Plan1.xlsx
	A																Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX	AY	AZ	BA	BB	BC	BD	BE	BF	BG	BH	BI	BJ	BK	BL	BM	BN	BO	BP	BQ	BR	BS	BT	BU	BV	BW
1	Material																Element	Prior	2023/47	2023/48	2023/49	2023/50	2023/51	2023/52	2024/1	2024/2	2024/3	2024/4	2024/5	2024/6	2024/7	2024/8	2024/9	2024/10	2024/11	2024/12	2024/13	2024/14	2024/15	2024/16	2024/17	2024/18	2024/19	2024/20	2024/21	2024/22	2024/23	2024/24	2024/25	2024/26	2024/27	2024/28	2024/29	2024/30	2024/31	2024/32	2024/33	2024/34	2024/35	2024/36	2024/37	2024/38	2024/39	2024/40	2024/41	2024/42	2024/43	2024/44	2024/45	2024/46	2024/47	2024/00	2024/01	2024/02	Total
2	1111																TotReq	582	560	0	0	##	0	0	0	0	0	##	0	##	0	##	0	##	0	0	0	0	0	0	##	0	##	0	0	0	##	0	0	0	##	0	0	0	0	##	0	##	0	##	0	0	0	##	0	0	##	0	0	0	0	0	##	0	##
3	1111																PIRCon	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
4	1111																CusReq	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
5	1111																CusAck	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
6	1111																SPLY	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	##	##	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	##	0	0	0	##	0	0	0	0	0	0	0	0	0	##
7	1111																PAB	1354	794	794	##	##	##	##	##	##	##	##	##	##	##	##	##	##	##	##	##	##	##	##	##	##	##	##	##	##	##	##	##	##	##	##	##	##	##	##	##	##	##	##	##	##	##	##	##	##	##	##	##	##	##	##	##	##	0
8	2222																TotReq	1186	9520	0	##	0	##	0	##	0	##	##	0	0	##	##	##	##	##	##	##	##	##	##	##	##	##	##	##	##	##	##	##	##	##	##	##	##	##	##	##	##	##	##	##	##	##	##	##	##	##	##	##	##	##	##	##	##	##
9	2222																PIRCon	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
10	2222																CusReq	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
11	2222																CusAck	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
12	2222																SPLY	398	0	0	##	##	0	0	0	0	0	##	##	##	##	##	##	0	##	0	##	0	0	##	0	##	0	0	##	0	0	##	0	0	##	0	0	##	##	0	0	0	##	##	##	0	0	##	##	##	##	##	0	0	0	##	0	0	##
13	2222																PAB	-26526	-36046	-36046	##	##	##	##	##	##	##	##	##	##	##	##	##	##	##	##	##	##	##	##	##	##	##	##	##	##	##	##	##	##	##	##	##	##	##	##	##	##	##	##	##	##	##	##	##	##	##	##	##	##	##	##	##	##	0
14	3333																TotReq	0	0	2240	0	0	0	0	##	0	0	##	##	0	##	0	##	##	##	##	##	##	##	##	##	##	##	##	##	##	##	##	##	##	##	##	##	##	##	##	##	##	##	##	##	##	##	##	##	##	##	##	##	##	##	##	##	##	##
15	3333																PIRCon	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
NewDemand

Cell Formulas
Range	Formula
S7	S7	=R7-S2

 

[Asbestos_Jen]

There are three match arguments in your formula, which seems to be the problem. When I used your formula but removed the third match, it worked for me. If TotReq is always the first occurrence of your material number, that's the row it will return, so I don't think you need it.

Book1
	A	B	C	D	E	F	G	H	I	J	K
1			1111
2
3
4
5
6
7
8			AVG Weekly Usage:			NOTES:
9
10
11
12
13
14
15
16			Start	End	Period C.Y.	On-Hand	Demand	Supply	Note
17			45241	45247	Prior	582	100	0
18			45248	45254	2023/47	482	560	0	G18 - should pull in 560
19			45255	45261	2023/48	-78	0	0
20			45262	45268	2023/49	-78	0	0
21			45269	45275	2023/50	-78	280	0	G21 - should pull in 280
22			45276	45282	2023/51	-358	0	0
23			45283	45289	2023/52	-358	0	0
24			45290	45296	2024/1	-358	0	500
25			45297	45303	2024/2	142	0	0
26			45304	45310	2024/3	142	0	0
27			45311	45317	2024/4	142	560	0
28			45318	45324	2024/5	-418	0	0
29			45325	45331	2024/6	-418	280	0
30			45332	45338	2024/7	-698	0	0
31			45339	45345	2024/8	-698	280	0
32			45346	45352	2024/9	-978	0	5000
33			45353	45359	2024/10	4022	280	0
34			45360	45366	2024/11	3742	0	0
35			45367	45373	2024/12	3742	0	0
36			45374	45380	2024/13	3742	0	0
37			45381	45387	2024/14	3742	0	0
38			45388	45394	2024/15	3742	0	0
39			45395	45401	2024/16	3742	0	0
40			45402	45408	2024/17	3742	280	500
41			45409	45415	2024/18	3962	0	0
42			45416	45422	2024/19	3962	560	0
43			45423	45429	2024/20	3402	0	0
44			45430	45436	2024/21	3402	0	0
45			45437	45443	2024/22	3402	0	0
46			45444	45450	2024/23	3402	560	0
47			45451	45457	2024/24	2842	0	0
48			45458	45464	2024/25	2842	0	5000
49			45465	45471	2024/26	7842	0	0
50			45472	45478	2024/27	7842	280	0
51			45479	45485	2024/28	7562	0	0
52			45486	45492	2024/29	7562	0	0
53			45493	45499	2024/30	7562	0	0
54			45500	45506	2024/31	7562	0	0
55			45507	45513	2024/32	7562	280	0
56			45514	45520	2024/33	7282	0	500
57			45521	45527	2024/34	7782	280	0
58			45528	45534	2024/35	7502	0	0
59			45535	45541	2024/36	7502	280	0
60			45542	45548	2024/37	7222	0	0
61			45549	45555	2024/38	7222	0	0
62			45556	45562	2024/39	7222	0	0
63			45563	45569	2024/40	7222	560	0
64			45570	45576	2024/41	6662	0	900
65			45577	45583	2024/42	7562	0	0
66			45584	45590	2024/43	7562	280	0
67			45591	45597	2024/44	7282	0	0
68			45598	45604	2024/45	7282	0	0
69			45605	45611	2024/46	7282	0	0
70			45612	45618	2024/47	7282	0	0
71			45619	45625	2024/48	7282	#N/A	0	These don't appear in the table
72			45626	45632	2024/49	#N/A	#N/A	2000
73			45633	45639	2024/50	#N/A	#N/A	0
74			45640	45646	2024/51	#N/A	#N/A	0
75			45647	45653	2024/52	#N/A	#N/A	0
DemandPlan

Cell Formulas
Range	Formula
F18:F75	F18	=F17-G17+H17
G18:G75	G18	=INDEX(NewDemand!$A$1:$CA$55,MATCH($C$1,NewDemand!$A$1:$A$55,0),MATCH($E18,NewDemand!$A$1:$CA$1,0))

 

[Tbaileyco]

Thank you for your Quick Reply Jen!

I am hoping to use the same formula to pull in the Supply ("SPLY") from the same data set, That is the reason for tying to get the last match in the formula that doesn't seem to be working as I want!

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[Asbestos_Jen]

I'm working with your data using instructions based on INDEX MATCH with Multiple Criteria (With Examples), specifically the section called "INDEX MATCH with Multiple Criteria Excel Formula" and got the below. See if this works for you.

2023-11-28.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1			1111												TotReq
2															PIRCon
3															CusReq
4															CusAck
5															SPLY
6															PAB
7
8			AVG Weekly Usage:			NOTES:
9
10
11
12
13
14
15
16			Start	End	Period C.Y.	On-Hand	Demand	Supply	Note
17			45241	45247	Prior	582	100	0
18			45248	45254	2023/47	482	560	0	G18 - should pull in 560
19			45255	45261	2023/48	-78	0	0
20			45262	45268	2023/49	-78	0	0
21			45269	45275	2023/50	-78	280	0	G21 - should pull in 280
22			45276	45282	2023/51	-358	0	0
23			45283	45289	2023/52	-358	0	0
24			45290	45296	2024/1	-358	0	0
25			45297	45303	2024/2	-358	0	0
26			45304	45310	2024/3	-358	0	0
27			45311	45317	2024/4	-358	560	0
28			45318	45324	2024/5	-918	0	0
29			45325	45331	2024/6	-918	280	0
30			45332	45338	2024/7	-1198	0	0
31			45339	45345	2024/8	-1198	280	0
32			45346	45352	2024/9	-1478	0	0
33			45353	45359	2024/10	-1478	280	0
34			45360	45366	2024/11	-1758	0	0
35			45367	45373	2024/12	-1758	0	0
36			45374	45380	2024/13	-1758	0	0
37			45381	45387	2024/14	-1758	0	0
38			45388	45394	2024/15	-1758	0	0
39			45395	45401	2024/16	-1758	0	0
40			45402	45408	2024/17	-1758	280	0
41			45409	45415	2024/18	-2038	0	0
42			45416	45422	2024/19	-2038	560	0
43			45423	45429	2024/20	-2598	0	0
44			45430	45436	2024/21	-2598	0	0
45			45437	45443	2024/22	-2598	0	0
46			45444	45450	2024/23	-2598	560	0
47			45451	45457	2024/24	-3158	0	0
48			45458	45464	2024/25	-3158	0	0
49			45465	45471	2024/26	-3158	0	0
50			45472	45478	2024/27	-3158	280	0
51			45479	45485	2024/28	-3438	0	0
52			45486	45492	2024/29	-3438	0	0
53			45493	45499	2024/30	-3438	0	0
54			45500	45506	2024/31	-3438	0	0
55			45507	45513	2024/32	-3438	280	0
56			45514	45520	2024/33	-3718	0	0
57			45521	45527	2024/34	-3718	280	0
58			45528	45534	2024/35	-3998	0	0
59			45535	45541	2024/36	-3998	280	0
60			45542	45548	2024/37	-4278	0	0
61			45549	45555	2024/38	-4278	0	0
62			45556	45562	2024/39	-4278	0	0
63			45563	45569	2024/40	-4278	560	0
64			45570	45576	2024/41	-4838	0	0
65			45577	45583	2024/42	-4838	0	0
66			45584	45590	2024/43	-4838	280	0
67			45591	45597	2024/44	-5118	0	0
68			45598	45604	2024/45	-5118	0	0
69			45605	45611	2024/46	-5118	0	0
70			45612	45618	2024/47	-5118	0	0
71			45619	45625	2024/48	-5118	#N/A	#N/A	These don't appear in the table
72			45626	45632	2024/49	#N/A	#N/A	#N/A
73			45633	45639	2024/50	#N/A	#N/A	#N/A
74			45640	45646	2024/51	#N/A	#N/A	#N/A
75			45647	45653	2024/52	#N/A	#N/A	#N/A
DemandPlan

Cell Formulas
Range	Formula
F18:F75	F18	=F17-G17+H17
G18:G75	G18	=INDEX(NewDemand!$A$1:$CA$55,MATCH($C$1&$O$1,NewDemand!$A$1:$A$55&NewDemand!$Q$1:$Q$55,0),MATCH($E18,NewDemand!$A$1:$CA$1,0))
H18:H75	H18	=INDEX(NewDemand!$A$1:$CA$55,MATCH($C$1&$O$5,NewDemand!$A$1:$A$55&NewDemand!$Q$2:$Q$55,0),MATCH($E18,NewDemand!$A$1:$CA$1,0))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Tbaileyco]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

I have updated it to 365! Thank you for the suggestion.

 

[Tbaileyco]

Jen,

Works perfectly !!!!!! Thank you so much!!!!!

 

[Fluff]

I have updated it to 365!

Thanks for that.
How about

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1			1111												TotReq
2															PIRCon
3															CusReq
4															CusAck
5															SPLY
6															PAB
7
8			AVG Weekly Usage:			NOTES:
9
10
11
12
13
14
15
16			Start	End	Period C.Y.	On-Hand	Demand	Supply	Note
17			45241	45247	Prior	582	100	0
18			45248	45254	2023/47	482	560	0	G18 - should pull in 560
19			45255	45261	2023/48	-78	0	0
20			45262	45268	2023/49	-78	0	0
21			45269	45275	2023/50	-78	280	0	G21 - should pull in 280
22			45276	45282	2023/51	-358	0	0
23			45283	45289	2023/52	-358	0	0
24			45290	45296	2024/1	-358	0	500
25			45297	45303	2024/2	142	0	0
26			45304	45310	2024/3	142	0	0
27			45311	45317	2024/4	142	560	0
28			45318	45324	2024/5	-418	0	0
29			45325	45331	2024/6	-418	280	0
30			45332	45338	2024/7	-698	0	0
31			45339	45345	2024/8	-698	280	0
32			45346	45352	2024/9	-978	0	5000
33			45353	45359	2024/10	4022	280	0
34			45360	45366	2024/11	3742	0	0
35			45367	45373	2024/12	3742	0	0
36			45374	45380	2024/13	3742	0	0
37			45381	45387	2024/14	3742	0	0
38			45388	45394	2024/15	3742	0	0
39			45395	45401	2024/16	3742	0	0
40			45402	45408	2024/17	3742	280	500
41			45409	45415	2024/18	3962	0	0
42			45416	45422	2024/19	3962	560	0
43			45423	45429	2024/20	3402	0	0
44			45430	45436	2024/21	3402	0	0
45			45437	45443	2024/22	3402	0	0
46			45444	45450	2024/23	3402	560	0
47			45451	45457	2024/24	2842	0	0
48			45458	45464	2024/25	2842	0	5000
49			45465	45471	2024/26	7842	0	0
50			45472	45478	2024/27	7842	280	0
51			45479	45485	2024/28	7562	0	0
52			45486	45492	2024/29	7562	0	0
53			45493	45499	2024/30	7562	0	0
54			45500	45506	2024/31	7562	0	0
55			45507	45513	2024/32	7562	280	0
56			45514	45520	2024/33	7282	0	500
57			45521	45527	2024/34	7782	280	0
58			45528	45534	2024/35	7502	0	0
59			45535	45541	2024/36	7502	280	0
60			45542	45548	2024/37	7222	0	0
61			45549	45555	2024/38	7222	0	0
62			45556	45562	2024/39	7222	0	0
63			45563	45569	2024/40	7222	560	0
64			45570	45576	2024/41	6662	0	900
65			45577	45583	2024/42	7562	0	0
66			45584	45590	2024/43	7562	280	0
67			45591	45597	2024/44	7282	0	0
68			45598	45604	2024/45	7282	0	0
69			45605	45611	2024/46	7282	0	0
70			45612	45618	2024/47	7282	0	0
71			45619	45625	2024/48	7282	#N/A	0
72			45626	45632	2024/49	#N/A	#N/A	2000
73			45633	45639	2024/50	#N/A	#N/A	0
74			45640	45646	2024/51	#N/A	#N/A	0
75			45647	45653	2024/52	#N/A	#N/A	0
Sheet2

Cell Formulas
Range	Formula
F18:F75	F18	=F17-G17+H17
G18:G75	G18	=INDEX(FILTER(NewDemand!$A$1:$CA$55,($C$1=NewDemand!$A$1:$A$55)*($O$1=NewDemand!$Q$1:$Q$55)),XMATCH($E18,NewDemand!$A$1:$CA$1))

 

[Asbestos_Jen]

Glad to help. Thanks for the feedback.

 

[Tbaileyco]

This solution works well too!!!!! Thank you!