+ Reply to Thread
Results 1 to 9 of 9

Match function does not work with multiple criteria

  1. #1
    Registered User
    Join Date
    12-08-2017
    Location
    United States
    MS-Off Ver
    2013
    Posts
    19

    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.
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    5,524

    Re: Match function does not work with multiple criteria

    Try these formulae in F2 to H2

    =MATCH(A2,'BE Grades All Terms'!D:D,0)
    =IF(ISNA(F2),"",IF(INDEX('BE Grades All Terms'!F:F,F2,1)=11,"Yes",""))
    =IF(G2="","",IF(INDEX('BE Grades All Terms'!I:I,'Full List '!F2,1)>'Full List '!A2,"Yes","No"))
    Last edited by mehmetcik; 01-03-2018 at 07:05 PM.
    Please Avoid Joining My List Of Blocked Users by:

    Saying Please and Thank you.

    Making requests not demands.

    Checking back on your post. I will not edit any post after 4 days.

    Marking threads as closed once your issue is resolved. How? The tools at the top

    Any reputation (*) points appreciated. None of us gets paid here.

    If you found someone's input useful, please take a second to click the * at the bottom left to let them know

  3. #3
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,621

    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
    Audere est facere

  4. #4
    Registered User
    Join Date
    12-08-2017
    Location
    United States
    MS-Off Ver
    2013
    Posts
    19

    Re: Match function does not work with multiple criteria

    Quote Originally Posted by daddylonglegs View Post
    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. #5
    Registered User
    Join Date
    12-08-2017
    Location
    United States
    MS-Off Ver
    2013
    Posts
    19

    Re: Match function does not work with multiple criteria

    Quote Originally Posted by mehmetcik View Post
    Try these formulae in F2 to H2

    =MATCH(A2,'BE Grades All Terms'!D:D,0)
    =IF(ISNA(F2),"",IF(INDEX('BE Grades All Terms'!F:F,F2,1)=11,"Yes",""))
    =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. #6
    Registered User
    Join Date
    12-08-2017
    Location
    United States
    MS-Off Ver
    2013
    Posts
    19

    Re: Match function does not work with multiple criteria

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

    ={MATCH(A9&"11",'BE Grades All Terms'!D:D&'BE Grades All Terms'!F:F,0)}

    But this one does not:

    ={MATCH(1, ('BE Grades All Terms'!D:D='Full List '!A9)*('BE Grades All Terms'!F:F="11"), 0)}

    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. #7
    Forum Expert
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    4,429

    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.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  8. #8
    Registered User
    Join Date
    12-08-2017
    Location
    United States
    MS-Off Ver
    2013
    Posts
    19

    Re: Match function does not work with multiple criteria

    Quote Originally Posted by JeteMc View Post
    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. #9
    Forum Expert
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    4,429

    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1