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

the above formula, still it doesn't work. I want to look up the value in H based on 3 criterias ; material, plant, storage location

Answer 3

In an earlier screenshot you were looking at N2 and N3. Now these are N1 and N2 but you didn't adjust the formula!! But that should just result in a zero.

Change the cell references and the formula should work. Perhaps you have a value error in any of the four columns referenced. Then SUMPRODUCT will throw out a value error as well. SIMIFS will work, though.

Answer 4

Also here: https://eileenslounge.com/viewtopic.php?t=38859