INDEX MATCH with multiple criteria returns N/A result, but match criteria look correct

Question

I am attempting to reshape a dataset in excel from long to wide using the index-match combination, but my match index formula returns NA's

I checked to see if the match criteria we're correctly specified using a combination of sumproducts and countifs. This gave me the expected result of 1.

I feel like I am making some dumb syntax error in the index-match code and would love a little guidance.

=INDEX(D_DATABASE!$AA$4:$BBT$260,
         MATCH(1,(AA$1=D_DATABASE!$N$4:$N$260)*
                 ($U4=D_DATABASE!$U$4:$U$260)*
                 (AA$3=D_DATABASE!$AA$3:$BBT$3),0))


 =SUMPRODUCT(COUNTIFS(AA$1,D_DATABASE!$N$4:$N$260,U4,
                       D_DATABASE!$U$4:$U$260,AA$3,
                       D_DATABASE!$AA$3:$BBT$3))

Have you stepped through the formula with the Formula Evaluate tool? You may want to use a smaller data set to see how the arrays are combined. — teylyn
Are you entering the first one as an array formula? (using Ctrl+Shift+Enter) — Tim Williams
Your row-wise equality check might need to be wrapped in TRANSPOSE - MATCH(1,(AA$1=D_DATABASE!$N$4:$N$260)* ($U4=D_DATABASE!$U$4:$U$260)* TRANSPOSE((AA$3=D_DATABASE!$AA$3:$BBT$3)),0) — Tim Williams
...though your arrays are not the same size (last one is much larger) so it's not clear what you're trying to discover here — Tim Williams
@TimWilliams , your last two comments hit the nail on the head. You should post that as the answer. — teylyn

Tim Williams Tim Williams · Accepted Answer · 2019-01-04T01:36:34

Here:

=INDEX(D_DATABASE!$AA$4:$BBT$260,
              MATCH(1,(AA$1=D_DATABASE!$N$4:$N$260)*
                      ($U4=D_DATABASE!$U$4:$U$260)*
                      (AA$3=D_DATABASE!$AA$3:$BBT$3),0))

...your last comparison is row-oriented vs columns for the other two, so you could try wrapping that in TRANSPOSE:

=INDEX(D_DATABASE!$AA$4:$BBT$260,
              MATCH(1,(AA$1=D_DATABASE!$N$4:$N$260)*
                      ($U4=D_DATABASE!$U$4:$U$260)*
                      TRANSPOSE((AA$3=D_DATABASE!$AA$3:$BBT$3)),0))

However, that last comparison also uses a much larger range than the previous two, so I'm not sure what you're trying to do with this.

EDIT after more info: you need to separate out that last row-wise search into its own MATCH

=INDEX(D_DATABASE!$AA$4:$BBT$260,
       MATCH(1,(AA$1=D_DATABASE!$N$4:$N$260)*($U4=D_DATABASE!$U$4:$U$260),0),
       MATCH(AA$3,D_DATABASE!$AA$3:$BBT$3,0))

INDEX MATCH with multiple criteria returns N/A result, but match criteria look correct

1 Answers