# Match function does not work with multiple criteria

1. ## Match function does not work with multiple criteria

Hello all,

I tried using a match function with multiple criteria but could not get it to work, even though when I evaluate the formula I see that Excel found a TRUE value. All I get are #N/A. I am using 0 for an exact search. When I used one criteria in an array I also got an error
{Match(1, (A:A=A2), 0)} but got it to find a match when I multiplied the array by 1 {Match(1, (A:A=A2)*1, 0)}. Why? However, that trick did not work for more criteria. I only filled in the first row but all rows return #N/A. It seems like a simple error that I cannot figure out.

Any help would be appreciated.

2. ## Re: Match function does not work with multiple criteria

Try these formulae in F2 to H2

=IF(G2="","",IF(INDEX('BE Grades All Terms'!I:I,'Full List '!F2,1)>'Full List '!A2,"Yes","No"))

3. ## Re: Match function does not work with multiple criteria

A:A=A2 returns an array of TRUE/FALSE values so you will never find a match for 1, use either

=MATCH(TRUE,A:A=A2,0)

or

=MATCH(1,(A:A=A2)*1,0)

The former is probably more efficient

In that latter version multiplying by 1 converts TRUE/FALSE values to 1/0 values.

If there are two conditions then multiplying the conditions together gives you 1/0 values anyway

If you still get #N/A results then you might have to look at whether the data types match, e.g. "11" text value won't match with 11 numeric value

4. ## Re: Match function does not work with multiple criteria

A:A=A2 returns an array of TRUE/FALSE values so you will never find a match for 1, use either

=MATCH(TRUE,A:A=A2,0)

or

=MATCH(1,(A:A=A2)*1,0)

The former is probably more efficient

In that latter version multiplying by 1 converts TRUE/FALSE values to 1/0 values.

If there are two conditions then multiplying the conditions together gives you 1/0 values anyway

If you still get #N/A results then you might have to look at whether the data types match, e.g. "11" text value won't match with 11 numeric value
Thank you for the clarification. I did not know about the difference. I tried to trim the course grades column, and convert to both number and text, but those techniques did not work. Do you have any other recommendations? I know the ID in row 9 took a 11 course.

Many thanks

5. ## Re: Match function does not work with multiple criteria

Originally Posted by mehmetcik
Try these formulae in F2 to H2

=IF(G2="","",IF(INDEX('BE Grades All Terms'!I:I,'Full List '!F2,1)>'Full List '!A2,"Yes","No"))
Thanks for the suggestions. The functions give me the row number of a grade, tells me if it is 11, and then I can adjust the third function to see if the term enrolled date is greater than the semester completed date. However, the match only provides the first row that shows up, which may not be a 11 course, and the third function has the same problem. If you have any suggestions on how to error check the match function please let me know. Thank you.

6. ## Re: Match function does not work with multiple criteria

Using the same workbook, this formula get me the right row:

But this one does not:

Aren't these the same? However, I need to use the latter formula because I have to add an additional criteria of being greater than the term enrolled date, and don't know how to do that with the ampersand. Thank you.

7. ## Re: Match function does not work with multiple criteria

Try removing the quotation marks that surround the number eleven in the second formula. Remember to activate by simultaneously pressing the Ctrl, Shift and Enter keys after making the change.
Let us know if you have any questions.

8. ## Re: Match function does not work with multiple criteria

Originally Posted by JeteMc
Try removing the quotation marks that surround the number eleven in the second formula. Remember to activate by simultaneously pressing the Ctrl, Shift and Enter keys after making the change.
Let us know if you have any questions.
That worked! I greatly appreciate your help!

9. ## Re: Match function does not work with multiple criteria

You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.

There are currently 1 users browsing this thread. (0 members and 1 guests)