+ Reply to Thread
Results 1 to 4 of 4

Using Large with multiple criteria to return a result from a corresponding cell

  1. #1
    Registered User
    Join Date
    02-21-2011
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    66

    Using Large with multiple criteria to return a result from a corresponding cell

    Howdy all,

    One I've tried multiple versions and scoured the internet for but cannot find the answer...

    I'm trying to return the Top 5 Destinations our employees travel to, and present them on a dashboard based on dropdown lists for the Snr Exec to choose from.

    So for example, they will choose the month, department, and then based on the highest total $ amount from a pivot table and these two criteria (or more) they will see the top 3 destinations.

    I've attached a workbook that had my final desperation attempt in Column I, and the array formula as follows:

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Col. C is the destination I would like returned, Col. A contains the month, Col. B the Department, and Col. D the amounts that I'm trying to find the Top 5 from largest to smallest to return the destination from Col. C.

    Any help would be massively appreciated please

    Thanks,
    Mike
    Attached Files Attached Files

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland... but I could be anywhere in the world
    MS-Off Ver
    O365 (PC) Version 2210
    Posts
    35,689

    Re: Using Large with multiple criteria to return a result from a corresponding cell

    Try this array formula:

    =INDEX($C$2:$C$52,MATCH(LARGE(IF($A$2:$A$52=$G$2,IF($B$2:$B$52=$H$2,$D$2:$D$52)),ROWS(I$2:I2)),$D$2:$D$52,0))

    or (if you prefer) a non-array formula... the array version is in the attached file.

    =INDEX($C$2:C$52,MATCH(AGGREGATE(14,6,$D$2:$D$52/(($A$2:$A$52=$G$2)*($B$2:$B$52=$H$2)),ROWS(I$2:I2)),$D$2:$D$52,0))
    Attached Files Attached Files
    Last edited by Glenn Kennedy; 01-10-2018 at 08:03 AM.
    Glenn



  3. #3
    Registered User
    Join Date
    02-21-2011
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    66

    Re: Using Large with multiple criteria to return a result from a corresponding cell

    Nailed it on the first go, thanks Glen!

  4. #4
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland... but I could be anywhere in the world
    MS-Off Ver
    O365 (PC) Version 2210
    Posts
    35,689

    Re: Using Large with multiple criteria to return a result from a corresponding cell

    You're welcome and thanks for the rep.

+ 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] Return LARGE with tiebreakers and title from result
    By augr in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 08-16-2016, 03:10 PM
  2. [SOLVED] Return result based on multiple criteria..
    By Frazzfreeman in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 09-02-2014, 03:47 AM
  3. Searching a table for multiple criteria to return single result
    By erice in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-16-2014, 09:53 AM
  4. Replies: 7
    Last Post: 09-06-2013, 11:59 PM
  5. Search for multiple criteria then return result
    By ctrapper in forum Excel General
    Replies: 7
    Last Post: 03-15-2011, 09:03 PM
  6. Return Result based on Multiple Criteria
    By franciz in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 09-20-2009, 01:30 PM
  7. Lookup multiple criteria in different tabs to return result
    By Nadir Soofi in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 06-06-2008, 06:28 PM

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