Results 1 to 7 of 7

Using INDEX MATCH and dealing with duplicate ranked values returned

Threaded View

  1. #1
    Registered User
    Join Date
    05-30-2013
    Location
    England
    MS-Off Ver
    Excel Mac V16.68 and Excel 2019
    Posts
    33

    Using INDEX MATCH and dealing with duplicate ranked values returned

    Hi Everyone,
    I'm struggling with how to solve this and not sure if it's possible by using the formula. I think there will only be 2 duplicate values maximum.

    I'm attaching a screenshot of a simplified version of the sheet and wonder if anyone can see if it's possible to overcome the duplicate values returned issue as my brain is melting down :D

    screen.jpg

    Cells C4 - C8 I'm trying to show the 'top 5' largest results in order from the data in F4 - G14.

    It all works, apart from if a value in column G is a duplicate value (ie they are both ranking joint 3rd with a value of "20" in this case, it gets confused and just puts "Agricultural Products" in C6 and C7 instead of "Agricultural Products" and "Air Freight & Logistics" (in 3rd or 4th, either way).

    (In case it helps, for example, D6 contains the formula:
    =INDEX($G$4:$G$14, MATCH(LARGE($G$4:$G$14, B6), $G$4:$G$14, 0))

    and C6 contains:

    =INDEX($F$4:$F$14, MATCH(LARGE($G$4:$G$14, B6), $G$4:$G$14, 0))

    I'm stuck and can't work it out and wondered if there's any clever trick or workaround anyone might be able to point me in the right direction? My brain is mush now

    Any help greatly appreciated,

    All the best,
    Paul
    Attached Files Attached Files
    Last edited by atombomb99; 11-19-2022 at 12:32 PM. Reason: Added sample xlsx

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Multiple Ranked Returns from Match and Index with same value (duplicate)
    By silentcarl in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 06-13-2019, 12:10 PM
  2. [SOLVED] Sum duplicate values returned by array formula INDEX(MATCH(LARGE(IF)))
    By TFiske in forum Excel Formulas & Functions
    Replies: 17
    Last Post: 12-02-2017, 07:18 PM
  3. [SOLVED] INDEX-MATCH: Dealing with missing lookup values
    By Big.Moe in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 02-19-2017, 02:08 AM
  4. [SOLVED] Values not returned by Index, Match when using values from Data Validation
    By Avinashch in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-10-2015, 10:34 AM
  5. Index Match returning values when none should be returned
    By Khaos1208 in forum Excel General
    Replies: 6
    Last Post: 04-27-2014, 04:48 PM
  6. displaying ranked values that are returned from COUNTIFS
    By HyZaret in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 04-17-2014, 07:52 PM
  7. Duplicate values returned using Index Match Lookup with Small()
    By jacob@thepenpoint in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 03-18-2013, 10:48 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