Share via

Index and match function not working with pivot tables

Anonymous
2022-10-29T08:23:04+00:00

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!

Microsoft 365 and Office | Excel | For business | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments
Answer accepted by question author
  1. riny 20,530 Reputation points Volunteer Moderator
    2022-10-29T10:48:59+00:00

    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.

    2 people found this answer helpful.
    0 comments

11 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. riny 20,530 Reputation points Volunteer Moderator
    2022-10-29T08:31:33+00:00

    Column A contains texts containing 14 numeric characters, all starting with a zero. M4 contains what seems to be a 13-digit number. Hence, no match will be found, #N/A as the result.

    0 comments
  2. Anonymous
    2022-10-29T08:39:51+00:00

    I included 0 in the M4 but it was not shown?

    0 comments
  3. Anonymous
    2022-10-29T08:42:33+00:00

    Can you have a look at this?

    0 comments
  4. riny 20,530 Reputation points Volunteer Moderator
    2022-10-29T09:21:57+00:00

    Can't tell from a screen shot. What do you get when you enter

    =M4=A5

    anywhere. I suppose these are supposed to be the same. If you see FALSE, then these cells are not equal. Do the same for N3 and C8. There could be trailing spaces or other invisible characters. Then MATCH will return an error.

    0 comments