INDEX MATCH Returning #N/A!

[jski21]

Good day Mr. Excel Team,

Here's a snapshot of the spreadsheet I'm working with:

Target-Vs-Actual.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
6	Grant	Grant Year	Grant Number	Authorized Amount	Amount Committed	Net Drawn Amount	Available to Draw	Committed %	Net Drawn %	Obligation Date	25%	50%	80%	90%	100%
7	CDBG	2015	B15MC390004	23,665,519	23,464,040	23,665,519	0.00	99.1%	100.0%	7/30/2015	12/30/2015	5/30/2016	8/30/2016	5/30/2017	7/30/2022
8	CDBG	2016	B16MC390004	21,139,545	20,779,642	20,727,151	412,394.04	98.3%	98.0%	9/22/2016	2/28/2017	7/31/2017	10/31/2017	7/31/2018	9/22/2023
9	CDBG	2017	B17MC390004	20,906,603	20,654,727	20,233,627	672,976.25	98.8%	96.8%	9/22/2017	2/28/2018	7/31/2018	10/31/2018	7/31/2019	9/22/2024
10	CDBG	2018	B18MC390004	22,286,611	20,447,090	19,544,265	2,742,346.78	91.7%	87.7%	8/22/2018	1/31/2019	6/30/2019	9/30/2019	6/30/2020	8/22/2025
11	CDBG	2019	B19MC390004	22,664,511	19,394,829	16,176,963	6,487,547.69	85.6%	71.4%	7/12/2019	12/31/2019	5/31/2020	8/31/2020	5/31/2021	7/12/2026
12	CDBG	2020	B20MC390004	29,197,197	21,594,476	18,577,904	10,619,292.56	74.0%	63.6%	6/18/2020	11/30/2020	4/30/2021	7/31/2021	4/30/2022	6/18/2027
13	CDBG	2021	B21MC390004	33,125,835	27,339,907	13,450,996	19,674,838.98	82.5%	40.6%	8/27/2021	1/31/2022	6/30/2022	9/30/2022	6/30/2023	8/27/2028
14	CDBG Total			172,985,822	153,674,710	132,376,426	40,609,396.30	88.8%	76.5%
15	ESG	2015	E15MC390004	1,781,536	1,781,536	1,781,536	0.00	100.0%	100.0%	7/30/2015	12/31/2015	5/31/2016	8/31/2016	5/31/2017	7/30/2017
16	ESG	2016	E16MC390004	1,761,226	1,761,226	1,761,226	0.00	100.0%	100.0%	9/22/2016	2/28/2017	7/31/2017	10/31/2017	7/31/2018	9/22/2018
17	ESG	2017	E17MC390004	1,744,977	1,744,977	1,744,977	0.00	100.0%	100.0%	9/22/2017	2/28/2018	7/31/2018	10/31/2018	7/31/2019	9/22/2019
18	ESG	2018	E18MC390004	1,739,682	1,739,682	1,739,682	0.00	100.0%	100.0%	8/22/2018	1/31/2019	6/30/2019	9/30/2019	6/30/2020	8/22/2020
19	ESG	2019	E19MC390004	1,796,770	1,796,770	1,796,770	0.00	100.0%	100.0%	7/12/2019	12/31/2019	5/31/2020	8/31/2020	5/31/2021	7/12/2021
20	ESG	2020	E20MC390004	1,858,769	1,858,769	1,858,769	0.00	100.0%	100.0%	6/18/2020	11/30/2020	4/30/2021	7/31/2021	4/30/2022	6/18/2022
21	ESG	2021	E21MC390004	1,852,356	1,852,356	-	0.00	100.0%	0.0%	8/27/2021	1/31/2022	6/30/2022	9/30/2022	6/30/2023	8/27/2023
22	ESG Total			12,535,316	12,535,316	10,682,960	0.00	100.0%	85.2%
23
24
25
26			25%	4	Grant	HOPWA
27			#N/A		Target
28	2015				CDBG
29	2016				HOME
30	2017				ESG
31	2018				HOPWA
32	2019
33	2020
34	2021
35	2022
Pivot (2)

Cell Formulas
Range	Formula
O7:O13	O7	=DATE(YEAR(J7)+7,MONTH(J7),DAY(J7))
K15:K21,K8:K13	K8	=EOMONTH(J8,$K$4)
L15:L21,L8:L13	L8	=EOMONTH(J8,$L$4)
M15:M21,M8:M13	M8	=EOMONTH(J8,$M$4)
N15:N21,N8:N13	N8	=EOMONTH(J8,$N$4)
O15:O21	O15	=DATE(YEAR(J15)+2,MONTH(J15),DAY(J15))
F26	F26	=INDEX(E28:E31,D26)
C26	C26	=Pivot!K6
C27	C27	=INDEX(K:K,MATCH(1,(Data!E9=A:A)*(A28=B:B)*(C26=K6:O6),0))
A28:A35	A28	=Data!A11
Press CTRL+SHIFT+ENTER to enter array formulas.

I'm a bit confounded as to why the INDEX MATCH formula in C27 won't pull the proper result (highlighted cell, K7). I thought I had everything lined up but evidently not. Any guidance would be appreciated.

Many thanks in advance.


jski

 

[Z51]

We don't have your 'Data' sheet, but maybe this format will work in C28 filled down. CTRL+SHIFT+ENTER to confirm array formula in older versions of Excel. Excel 365 doesn't require CSE.

=INDEX($K$7:$O$21,MATCH($F$26&$A28,$A$7:$A$21&$B$7:$B$21,0),MATCH($C$26,$K$6:$O$6,0))

 

[Fluff]

Another option

=INDEX(K:O,MATCH(1,(Data!E9=A:A)*(A28=B:B),0),MATCH(C26=K6:O6,0))

 

[jski21]

That worked pjoaquin. Your as well Fluff. Thanks! Wasn't aware that you could use "&" in that function.

Very instructive as usual gentlemen. Enjoy your day!

 

[Fluff]

Glad we could help & thanks for the feedback.