+ Reply to Thread
Results 1 to 7 of 7

INDEX MATCH & LARGE w/ multiple criteria

  1. #1
    Registered User
    Join Date
    12-15-2019
    Location
    new york
    MS-Off Ver
    2016
    Posts
    26

    INDEX MATCH & LARGE w/ multiple criteria

    Hello everyone,

    I am trying to filter out some data by product, country, and rank # (e.g the largest,second largest). The problem I am getting is that I am not able to figure out how to sort the largest number.

    So, when there are multiple countries with the same product #, the following function selects the first column instead of the largest number given those conditions.

    ={LARGE(INDEX(B3:E7,MATCH(1,(B3:B7=I3)*($C$3:$C$7=I4),0),4),I5)}

    I think the large function is misplaced. I have attached an image of the excel as I am not able to upload the file.

    Thanks!


    Capture.PNG

  2. #2
    Forum Expert Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Re: INDEX MATCH & LARGE w/ multiple criteria

    Try this:
    Please Login or Register  to view this content.
    Is that something you can work with?
    Ron
    Former Microsoft MVP - Excel (2006 - 2015)
    Click here to see the Forum Rules

  3. #3
    Registered User
    Join Date
    12-15-2019
    Location
    new york
    MS-Off Ver
    2016
    Posts
    26

    Re: INDEX MATCH & LARGE w/ multiple criteria

    Thanks for your response!

    This works, however what happens when I change the year? For example, the array references column E. Is there a way to automatically change the year and have the array go to the appropriate column without having to write a new formula?

    I want to allow the user to select the Year, Product, Country, and Rank. After they have selected these criteria, the formula will spit out the number. FYI, the year has to be on top, as the data provided is in that format.

  4. #4
    Forum Expert Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Re: INDEX MATCH & LARGE w/ multiple criteria

    Yes.... I didn't want to make the initial formula unduly complicated.
    Try this:
    Please Login or Register  to view this content.
    Is that something you can work with?

  5. #5
    Registered User
    Join Date
    12-15-2019
    Location
    new york
    MS-Off Ver
    2016
    Posts
    26

    Re: INDEX MATCH & LARGE w/ multiple criteria

    For some reason that works in the "example" workbook, but not on the actual file.

    I get a #NUM error that I can't figure out why it is happening. Attachment 654019

    Have attached a picture of the formula.

  6. #6
    Forum Expert Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Re: INDEX MATCH & LARGE w/ multiple criteria

    Then the problem is with your data. Apparently the picture is incapable of communicating the real issue.

  7. #7
    Registered User
    Join Date
    12-15-2019
    Location
    new york
    MS-Off Ver
    2016
    Posts
    26

    Re: INDEX MATCH & LARGE w/ multiple criteria

    So, I ended up figuring it out. Has to do with the format of the cells. The format has to be same for some reason. Thank you for your help!

    I really appreciate it

+ 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. [SOLVED] INDEX MATCH & LARGE Functions, Multiple Criteria
    By skyhawk3485 in forum Excel Formulas & Functions
    Replies: 15
    Last Post: 12-19-2018, 09:55 AM
  2. [SOLVED] INDEX MATCH & LARGE functions, multiple criteria
    By abulkhairi in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-11-2017, 01:20 AM
  3. Index, match large with multiple criteria pulling wrong record
    By markclary in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 09-29-2017, 08:00 AM
  4. [SOLVED] INDEX MATCH LARGE with Multiple criteria
    By clachi80 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 06-14-2017, 03:50 AM
  5. [SOLVED] Using LARGE or MAX inside INDEX-MATCH formula with multiple criteria
    By Big.Moe in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 02-28-2017, 02:40 AM
  6. Index Match Large formula with multiple criteria
    By Dylan Cooper in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 10-21-2016, 06:00 PM
  7. Excel 2007 : Index, Match, Large Formula: Multiple Criteria, Multiple Ranges
    By SimpleJack in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 07-19-2013, 08:54 AM

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