INDEX MATCH multiple criteria not working (#N/A)

Question

I am attempting to match 2 criteria (drug generic and drug pack size) in an INDEX MATCH formula to cross-reference 2 databases I have.

Despite reading several posts on this and other sites I just cannot get these to work. Working in Office 2010.

Formula 1 attempt (referencing 3rd column within INDEX with CTRL-SHIFT-ENTER):

=INDEX(i!$A$1:$F$2635, MATCH(1, ($U32=i!$A$1:$A$2635)*($Z32=i!$D$1:$D$2635), 0), 3)

Formula 2 attempt (default to single column within INDEX with CTRL-SHIFT-ENTER):

=INDEX(i!$A$1:$A$2635, MATCH(1, ($U32=i!$A$1:$A$2635)*($Z32=i!$D$1:$D$2635), 0))

Formula 3 attempt (default to single column within INDEX with sub-INDEX to avoid ARRAY requirements):

=INDEX(i!A:A, MATCH(1, INDEX(($U66=i!A:A)*($Z66=i!D:D), 0), 0))

Matching on a single criteria works well using the MATCH(1, EQUATION-ARRAY, 0) method. Two just does not work and always returns a #N/A.

I have confirmed that the data is there within both sheets to match, that there are no trailing or leading spaces, and that the match does return a result for a single criteria at a time. The issue is within MATCH(...) because I've pulled that element out on its own (take INDEX out of the testing) and still #N/A.

I am seeking to avoid concatenation at this point (eg. I could create a string of all my criteria to match against in both sheets) as I'm expecting partial matches that could still be valuable (eg. 4 of 5 criteria match, 3 of 5, etc).

I cannot find anything wrong with your formulas; all three return their respective correct value. Consider putting a redacted version of your worksheet(s) into a public file area for direct access. — user4039065
Agreed with Jeeped. There's absolutely nothing wrong with those formulas (though it should be pointed out that the last, by using entire column references, will have a huge impact on calculation time: using INDEX in this way may circumvent the need for CSE, though it in no way renders the resulting formula any less of an "array" one than the equivalent CSE version). — XOR LX
As one final test you may consider using the following formula: =COUNTIFS(i!$A$1:$A$2635, $U32, i!$D$1:$D$2635, $Z32) . If that returns 0 then you have no single row where column A is equal to U32 and column D is equal to Z32. — user4039065
How did you confirm that there was a match? What sort of data is in U32 and Z32? Sometimes, if those values are "numbers" you might get a "data mismatch" where a text-formatted number won't match with an actual number. For that reason Jeeped's suggested COUNTIFS test might give you a match where MATCH will not — barry houdini
Thank you for your comments and ideas. I will try the COUNIFS formula supplied by Jeeped and see if there's truly no matches. — James Grant

Unknown Unknown · Accepted Answer · 2015-05-28T07:48:22

Your sample data shows that a number has been split off a text string without converting it back to a true number. In short, a cell containing ="99" is not equal to a cell containing the number 99 even though COUNTIF or COUNTIFS function will say it does. Put a SUM function on SUM(a[pack]) and you will see it resolves as zero.

Your array formula for AB11 should be,

=INDEX(iphbin[generic_desc], MATCH(1, ($U11=iphbin[generic_desc])*(INT($Z11)=iphbin[pack_desc]), 0))
' ...or,
=INDEX(iphbin[generic_desc], MATCH(1, (a[@[generic (first only)]]=iphbin[generic_desc])*(INT(a[@pack])=iphbin[pack_desc]), 0))

Array formulas need to be finalized with Ctrl+Shift+Enter↵ (but you aleady knew that!).

To paraphrase, 'Text is Text and a Number is a Number and never the twain shall meet.' (even if they look the same)

FWIW, prefacing with a double unary (e.g. -- or double minus) is another common practise to converting a string that looks like text to an actual number.

INDEX MATCH multiple criteria not working (#N/A)

1 Answers