Index and match function not working with pivot tables

Question

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 and I appreciate if someone tells the vba code to process this!

Answer 1

Did some more testing. On a Pivot table this time. The PT headers cause a Value error when you multiply the first three arrays with H:H.

Include an IFERROR inside the SUMPRODUCT like this:

=SUMPRODUCT(IFERROR((A:A=M4)*(B:B=N2)*(C:C=N3)*H:H**,0)**)

As said earlier, SUMIFS doesn't have this problem.

Answer 2

By the way, why do you have three MATCH functions inside the INDEX function? That should return a #REF! error. What do you expect the formula to return?

Answer 3

I want the sum value as result which is in the column H

Answer 4

So, N2 should show 4 ?

If so, then try this:

=SUMPRODUCT((A:A=M4)*(B:B=N2)*(C:C=N3)*H:H)

or

=SUMIFS(H:H,A:A,M4,B:B,N2,C:C,N3)

Answer 5

Hi,

You can try this formula:

=SUM((A5:A1000=M4)*(B5:B1000=N2)*(C5:C1000=N3)*(D5:D1000))

If you don't use Microsoft 365 or Office 2021 then you can try this another one:

=SUMPRODUCT((A5:A1000=M4)*(B5:B1000=N2)*(C5:C1000=N3)*(D5:D1000))

Change the range in formula as per you need.