CODEWITHSUNDEEP
excelindexingmatchlookup
James Grant
James Grant

Reputation: 3

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

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).

Upvotes: 0

Views: 13114

Answers (1)

user4039065
user4039065

Reputation:

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.

Upvotes: 1

Related Questions