Closed Thread
Results 1 to 11 of 11

Index Match with Multiple Criteria (exact for text and greater than value for number)

  1. #1
    Registered User
    Join Date
    05-30-2015
    Location
    Singapore
    MS-Off Ver
    2010
    Posts
    7

    Exclamation Index Match with Multiple Criteria (exact for text and greater than value for number)

    Dear experts,

    I have a burning question on the Index Match Match formulas.

    I would like to have a formula in the highlighted yellow cells to help me match exactly the Origin-Destination lane and then approximate (round up) to the next weight interval; and return me the cost of that lane.

    My understanding of Index Match Match is to look up in horizontal and vertical columns which would not be feasible for this case; since both look up values are in vertical columns.

    Could any of you please advice on how I can do that?

    excel.JPG
    Attached Files Attached Files

  2. #2
    Forum Expert azumi's Avatar
    Join Date
    12-10-2012
    Location
    YK, Indonesia
    MS-Off Ver
    Excel 2019
    Posts
    2,360

    Re: Index Match with Multiple Criteria (exact for text and greater than value for number)

    Maybe somthing like this? using array formula.....

    cheers
    Attached Files Attached Files

  3. #3
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Index Match with Multiple Criteria (exact for text and greater than value for number)

    With an helpcolumn (see the yellow cells).

    After that an index / match formula.

    The result is in the green cells.

    See the attached file.
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  4. #4
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Index Match with Multiple Criteria (exact for text and greater than value for number)

    ... or with a non-array alternative (no helper):

    =INDEX($F$2:$F$7,MATCH(1,INDEX(($C$2:$C$7=$I2)*($D$2:$D$7=J2)*($E$2:$E$7>=K2),0),0))
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  5. #5
    Registered User
    Join Date
    05-30-2015
    Location
    Singapore
    MS-Off Ver
    2010
    Posts
    7

    Re: Index Match with Multiple Criteria (exact for text and greater than value for number)

    Thank you all for the solutions! They work perfectly!

  6. #6
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Index Match with Multiple Criteria (exact for text and greater than value for number)

    Thanks for the Reputation that you have added. It's much appreciated. If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

  7. #7
    Registered User
    Join Date
    05-04-2005
    Posts
    8

    Re: Index Match with Multiple Criteria (exact for text and greater than value for number)

    Trying to do the same with just one exact (or text) criteria and and input value "closest to" but less than closest value in the range criteria but when I use "less than" in the formula I get #N/A and am not sure why...
    I tried both of these methods - no luck

    =INDEX(returninforange,MATCH(1,INDEX((exactlookupvalue=lookuprange1)*(inputvalue<=inputrange),0),0))

    {=INDEX(returninforange,MATCH(inputvalue,IF(lookuprange1=exactlookupvalue,inputrange),-1))}

    I am sure I am missing something fundamental but cannot figure it out...

  8. #8
    Registered User
    Join Date
    05-04-2005
    Posts
    8

    Re: Index Match with Multiple Criteria (exact for text and greater than value for number)

    This worked.

    {=INDEX(returninforange,MATCH(TRUE,IF(lookuprange1=exactlookupvalue,inputrange>inputvalue),0))}

    For the previous index with MATCH -1, the data would have had to have been sorted from newest to oldest (matching date in my case) or largest to smallest.
    Still not sure why the first did not work.

  9. #9
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Index Match with Multiple Criteria (exact for text and greater than value for number)

    Glad you got sorted. However next time, don't hijack an old thread. Start your own, new, thread. (see Forum rules, rule 4)

  10. #10
    Registered User
    Join Date
    10-26-2021
    Location
    France
    MS-Off Ver
    2013
    Posts
    20

    Index Match with Multiple Criteria (incl. wildcard & greater than a value)

    Hello Excel experts
    I am trying to match 3 criteria (one with a wildcard) using INDEX and MATCH in the attached excel file;

    Exact DATE + Abbreviated FORMAT + No of trays >0

    I have tried 2 formulas that I would normally use but neither works exactly with the 3 definezd criteria
    I would very much appreciate your advice on where I am going wrong

    Many thanks
    Chris
    Attached Files Attached Files

  11. #11
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,363

    Re: Index Match with Multiple Criteria (exact for text and greater than value for number)

    Administrative Note:

    Welcome to the forum.

    We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

    Please see Forum Rule #4 about hijacking and start a new thread for your query.

    If you are not familiar with how to start a new thread see the FAQ: How to start a new thread
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

Closed Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Return value on multiple criteria without an exact match
    By Caco in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-27-2015, 04:29 PM
  2. Replies: 2
    Last Post: 09-27-2014, 04:34 PM
  3. [SOLVED] Index/Match - multiple criteria & greater than!
    By kennedy.clan in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 09-15-2013, 09:03 AM
  4. Index Matching with a nested Greater than function for multiple criteria
    By Solpics in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-05-2012, 12:22 AM

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