INDEX MATCH Returns #N/A

[jski21]

Good day Mr. Excel Team,

Can't quite get this INEX MATCH to return the highlighted value. Everything seem ok to me but Excel doesn't think so:

INDXMTCH.xlsx
	B	C	D	E	F	G
3
4	Sum of Drawn Amount	FY Year				2018
5	Activity	2017	2018	2019	2020	0
6	Administration	41,586	67,973	28,952	22,353	0
7	Data Collection (HMIS)	9,728	20,445	9,168	5,702	#N/A
8	Homeless Prevention	104,569	34,255	43,435	11,109
9	Rapid Re-Housing	726,680	733,707	314,130	669,010
10	Shelter	741,224	957,449	1,044,875	1,041,667
11	Grand Total	1,623,787	1,813,830	1,440,560	1,749,842
Pivot

Cell Formulas
Range	Formula
G7	G7	=INDEX($C$6:$F$10,MATCH("Homeless Prevention",$B$6:$B$10,0),MATCH($G$4,$C$5:$F$5,0))

Thanks in advance for the look/see and advice.

 

[jasonb75]

Formula looks fine to me and works correctly when I copy your mini sheet to excel.

Best guess is that G4 is / was formatted as text so is not a valid number to compare to C5:F5. Try formatting G4 as General / Number, then re-enter the year into it.

 

[Snakehips]

@jski21 Same here.
Having pasted the above XL2B into a worksheet here, it works perfectly!
Do you have any discrepancy in your Activity text such as hidden space?

 

[jski21]

Ok...tried both General and Number on all cells and still #N/A. Activity text is clean.

This is a pivot table but I didn't click on the table to obtain the range/cell references. Just entered them to keep things clean. Shouldn't make a difference, correct?

Curiouser and curiouser as Lewis Carroll would say...

 

[jasonb75]

I think that headers are text by default with a pivot table.

=INDEX($C$6:$F$10,MATCH("Homeless Prevention",$B$6:$B$10,0),MATCH($G$4&"",$C$5:$F$5,0))

 

[jski21]

Bingo! That nuance did the trick jasonb75.

Thanks jasonb75 & Snakechips for your time and consideration today. Well done!