+ Reply to Thread
Results 1 to 5 of 5

Accounting for duplicates with LARGE function (within index match)

  1. #1
    Registered User
    Join Date
    03-22-2017
    Location
    Canada
    MS-Off Ver
    2010
    Posts
    10

    Accounting for duplicates with LARGE function (within index match)

    Hi there!

    I have come across an interesting 'bug'.

    In my data, I am getting the top 5 values across a row, as well as extracting the column header title. However, I have come across an issue when there are identical values in the row: only the first column header is being pulled, instead of the actual header for the second identical value. I have tried to illustrate the scenario in the attached example.

    Any advice about how to have duplicates properly included with this?

    Thanks!
    Attached Files Attached Files
    Last edited by quart; 04-27-2017 at 05:03 PM. Reason: make attachment more clear

  2. #2
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: Accounting for duplicates with LARGE function (within index match)

    not really a bug, its working as intended
    match will always looks up the 1st instance of the number

    in order to look for the second instance of the number you need to do some additional work
    see attached for purposed solution using helper row which is based off rank + some additional data
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    i used only 3 decimal places as your example is only integers but its suggested you use a lot more if you are working with decimals '
    ie make 0.001 to 0.000000001 or something like that
    Attached Files Attached Files
    If you are satisfied with the solution(s) provided, please mark your thread as Solved.
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

  3. #3
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,409

    Re: Accounting for duplicates with LARGE function (within index match)

    No helper

    Please Login or Register  to view this content.
    Quang PT

  4. #4
    Registered User
    Join Date
    03-22-2017
    Location
    Canada
    MS-Off Ver
    2010
    Posts
    10

    Re: Accounting for duplicates with LARGE function (within index match)

    Quote Originally Posted by humdingaling View Post
    not really a bug, its working as intended
    match will always looks up the 1st instance of the number

    in order to look for the second instance of the number you need to do some additional work
    see attached for purposed solution using helper row which is based off rank + some additional data
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    i used only 3 decimal places as your example is only integers but its suggested you use a lot more if you are working with decimals '
    ie make 0.001 to 0.000000001 or something like that
    You're right - it's not a bug, per se.... just not doing what I wish it were!

    I like your solution with ranking - probably should have considered that myself. Takes a while to run (hundreds of columns/rows), but I'm getting the correct results! I changed the factor to 0.00000001 as suggested since I was working with percentages in the first place. Also had to move my index match pointers around a bit, and everything is running smoothly.

    Thanks a lot!
    Last edited by quart; 04-28-2017 at 10:40 AM.

  5. #5
    Registered User
    Join Date
    03-22-2017
    Location
    Canada
    MS-Off Ver
    2010
    Posts
    10

    Re: Accounting for duplicates with LARGE function (within index match)

    Quote Originally Posted by bebo021999 View Post
    No helper

    Please Login or Register  to view this content.
    It took me a few minutes to understand your syntax, but that's certainly an interesting solution! Given how my data is setup, I opted for the ranking solution noted above. Thanks for your help!

+ 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. Issue with LARGE, MATCH, INDEX and duplicates, multiple conditions
    By Toddowhams in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 01-05-2017, 03:08 PM
  2. INDEX/MATCH with =LARGE is giving duplicates
    By keith740 in forum Excel General
    Replies: 6
    Last Post: 10-07-2015, 03:35 AM
  3. [SOLVED] Problem Using LARGE formula with Index/Match to pull values when there are Duplicates
    By BDavis in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 08-11-2014, 02:23 PM
  4. Index match with large function
    By morobo in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-19-2014, 02:27 PM
  5. [SOLVED] Large,Index, Match with duplicates
    By x65140 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-10-2014, 10:45 AM
  6. [SOLVED] Accounting for duplicates when using INDEX/MATCH forumulas
    By MaddyG in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 12-22-2012, 09:40 AM
  7. Using Index & Match with the Large function
    By JXH in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-24-2011, 08:39 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