Index & Match formula error

[satish gubbi]

Hi Team,

I have the attached workbook updated with Index and Match formula and its working partially correct and while selecting last cell for NonAllocation criteria. it is showing error.

Kindly help me in rectifying the error.

[JBeaucaire]

This is how I would do that, I'm not sure your prior formula is working for any B4 selection. This does work. It will mark any cell as #N/A if that selection does not exist anywhere in the data.

=INDEX(Data!$D$3:$S$36, MATCH($B$4&"-"&D$3, INDEX(Data!$B$3:$B36&"-"&Data!$C$3:$C$36, 0), 0), MATCH($C4, Data!$D$2:$S$2, 0))

[satish gubbi]

Hi JBeaucaire,

Thank you very much for your help, this formula is working as intended.