+ 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
    2016
    Posts
    53

    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
    8,448

    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.
    My General Rules if you want my help. Not aimed at any person in particular:

    1. Please Make Requests not demands, none of us get paid here.

    2. Check back on your post regularly. I will not return to a post after 4 days.
    If it is not important to you then it definitely is not important to me.

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

    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
    2016
    Posts
    53

    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
    2016
    Posts
    53

    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
    2016
    Posts
    53

    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 Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,420

    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
    2016
    Posts
    53

    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 Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,420

    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)

Similar Threads

  1. Need help in Index, Match usage to match multiple criteria in sum function
    By Summer0830 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 03-26-2017, 02:47 AM
  2. Index Match multiple criteria wont work, but nested IF's do-partially
    By andrewoz in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-17-2016, 02:12 AM
  3. MATCH Function - Multiple Criteria
    By Stressed_Daniel in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-22-2015, 11:20 PM
  4. URGENT HELP! Match function with multiple criteria
    By nickynec in forum Excel General
    Replies: 10
    Last Post: 09-02-2015, 03:36 PM
  5. [SOLVED] Index-Match function with multiple criteria
    By MikeSta4ord in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 06-05-2015, 12:51 PM
  6. Index and match function multiple criteria
    By mfortier3 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-16-2013, 05:27 AM
  7. Replies: 7
    Last Post: 09-06-2009, 03:23 PM

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