Index match and code

[chandg11]

Hi Experts,

I received #N/A error when using th formula =INDEX(H:H,MATCH(M4,A:A,0),MATCH(N2,B:B,0),MATCH(N3,C:C,0)) in the table. How the error should be rectified by using the index and match formula in the green cells and as well as I appreciate if someone tells the vba code to process this!

[chandg11]

Hi Experts,

I received #N/A error when using th formula =INDEX(H:H,MATCH(M4,A:A,0),MATCH(N2,B:B,0),MATCH(N3,C:C,0)) in the table. How the error should be rectified by using the index and match formula in the green cells and as well as I appreciate if someone tells the vba code to process this!

[HansV]

Welcome to Eileen's Lounge!

The formula is not correct - you cannot use three instances of MATCH next to each other. You have to combine them into a single MATCH. Moreover, the values in columns B and C are text values, while N1 and N2 contain numbers.
The formula in N4 should be

=INDEX($H$5:$H$51,MATCH(1, ($A$5:$A$51=$M4)*($B$5:$B$51=N$1&"")*($C$5:$C$51=N$2&""),0))

If you don't have Microsoft 365 or Office 2021, confirm the formula by pressing Ctrl+Shift+Enter.
The fill down and to the right.
Most of the cells will still return #N/A because there are no matches.

S1932.png

[HansV]

Alternatively, you could use the source range of the pivot table for your calculations:

=SUMPRODUCT(Sheet1!$F$2:$F$2049*(Sheet1!$C$2:$C$2049=$M4)*(Sheet1!$A$2:$A$2049=N$1&"")*(Sheet1!$B$2:$B$2049=N$2&""))+SUMPRODUCT(Sheet1!$H$2:$J$2049*(Sheet1!$C$2:$C$2049=$M4)*(Sheet1!$A$2:$A$2049=N$1&"")*(Sheet1!$B$2:$B$2049=N$2&""))

[chandg11]

Thank you so much