+ Reply to Thread
Results 1 to 4 of 4

Index Match Array with Duplicates

  1. #1
    Registered User
    Join Date
    02-06-2014
    Location
    Columbus, OH
    MS-Off Ver
    Excel 2007
    Posts
    12

    Index Match Array with Duplicates

    I am working on a report to show the top 20 producers in a given state that are on target for an award. It worked all well and good last month, but this month I encountered an issue with the names not accurately reflecting the results. I have attached a sample file below. The worksheet "Tracker" contains source data (which is hidden with the file locked) while the "Summary" worksheet is the user facing sheet that shows up to the top 20 producers in each state.

    The issue I'm encountering is pulling back the names if there are duplicate point values. In the sample file, there are two producers with 100,000 points; one in DE and one in OH. When I select DE, the name is correct but when I select OH, because the lookup is off of the point value, it's still pulling back the DE producers name. Since they are being ranked based on points, I'm not sure how else to perform the lookup. I don't want to add a helper column on the Tracker worksheet because it is a file that comes from another source and I want any future person in my role to be able to copy, paste, and send without manual adjustments. I'm sure I'm missing something simple, but I can't figure out what.

    Thank you for your help.

    Test File.xlsx

  2. #2
    Forum Expert
    Join Date
    09-11-2014
    Location
    Washington, DC
    MS-Off Ver
    2016
    Posts
    1,907

    Re: Index Match Array with Duplicates

    If I'm understanding your question correctly, try this in B8:

    Please Login or Register  to view this content.
    and this in C8, entered as an array formula and confirmed with CTRL+SHIFT+ENTER:

    Please Login or Register  to view this content.
    Then drag across/down to fill.
    Last edited by mcmahobt; 05-08-2015 at 11:31 AM. Reason: Read problem statement wrong. Corrected.
    Spread the love, add to the Rep

    "None of us are as smart as all of us."

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

    Re: Index Match Array with Duplicates

    Try this out (green coloured cells). Some of these are Array formulas.


    They're a little different from ordinary formulas in that they MUST be confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER.

    You will know the array is active when you see curly braces { } appear around the outside of your formula. If you do not CTRL+SHIFT+ENTER you will get an error message or an incorrect answer. Press F2 on that cell and try again.

    Don't type the curly braces yourself - it won't work...
    Attached Files Attached Files
    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

  4. #4
    Registered User
    Join Date
    02-06-2014
    Location
    Columbus, OH
    MS-Off Ver
    Excel 2007
    Posts
    12

    Re: Index Match Array with Duplicates

    I tried Glenn's response first and it worked amazingly. Thank you both for your responses!

+ 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. Replies: 6
    Last Post: 04-30-2014, 02:42 AM
  2. [SOLVED] Identify Duplicates and Non-Duplicates within an Array (Match)
    By simpson11 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 02-26-2013, 09:49 AM
  3. Replies: 2
    Last Post: 03-16-2012, 12:03 PM
  4. Index, match, duplicates in array - how to pick up the last/bottom line
    By rain4u in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 04-25-2011, 08:02 PM
  5. Index, Match and duplicates
    By hazelwouldbe in forum Excel General
    Replies: 4
    Last Post: 04-03-2011, 06:48 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