Share via

How to avoid duplicates when using an Index Match formula

Anonymous
2023-08-25T20:18:22+00:00

I'm trying to rank order entries using INDEX MATCH, and it's working, but when there are duplicate values, it only returns the first entry (see attached image).

Here are the formulas I used:

C5: =LARGE(C13:C207,A5:A9)

B5: =INDEX(B13:B207,MATCH($C5,C13:C207,0))

Microsoft 365 and Office | Excel | For business | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

8 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. HansV 462.4K Reputation points MVP Volunteer Moderator
    2023-08-25T20:26:48+00:00

    Clear B5:C9.

    In B5, enter the formula

    =INDEX(SORT(B13:C207, 2), SEQUENCE(5), SEQUENCE(, 2))

    0 comments
  2. Anonymous
    2023-08-25T20:37:45+00:00

    Thank you so much for replying! When I do that, cells B6-B9 look like that

    0 comments
  3. HansV 462.4K Reputation points MVP Volunteer Moderator
    2023-08-25T21:20:15+00:00

    What is the formula in B5? Exactly the one that I posted?

    0 comments
  4. Anonymous
    2023-08-25T21:28:49+00:00

    When I do exactly the one you posted, this is what I get. In the screenshot above, I removed the comma in from of the 2 at the end and that was the result.

    Thank you so much for helping me!

    0 comments
  5. HansV 462.4K Reputation points MVP Volunteer Moderator
    2023-08-25T21:30:33+00:00

    Please clear C5:C9.

    0 comments