Forum Discussion

Copper Contributor

Feb 13, 2023

Solved

Excel 2016 INDEX MATCH fixing duplicate entries

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 (note the duplicates "General Question" and "Capability Requ...

excel

Formulas and Functions

OliverScheurich
Feb 13, 2023
jbkalla
=INDEX($A$2:$A$10,MATCH(TRUE,($B$2:$B$10=LARGE($B$2:$B$10,ROW(E1))*(COUNTIF($F$1:F1,$A$2:$A$10)=0)),0))
You can try this formula. Enter the formula with ctrl+shift+enter as an arrayformula.

jbkalla

Copper Contributor

Feb 13, 2023

OliverScheurich& Patrick2788 : So if I move the rows, it no longer works, even though the formulas are still looking at the correct locations. What's causing that? See additional attached file.

http://kalla.net/files/kalla_exampleTable2.xlsx

Is there something about the formula that requires the cells to start at the top?

JKPieterse: I'm unable to sort the original columns, so I'm guessing your solution won't work for my case. But thank you!

OliverScheurich

Gold Contributor

Feb 13, 2023

jbkalla

=INDEX($A$56:$A$64,MATCH(TRUE,($B$56:$B$64=LARGE($B$56:$B$64,ROW(E1))*(COUNTIF($F$55:F55,$A$56:$A$64)=0)),0))

Within the LARGE formula ROW(E1) evaluates to 1. When the formula is filled down ROW(E2), ROW(E3) and so on evaluate to 2, 3 and so on. Then LARGE returns the 1st, 2nd and 3rd largest result. ROW(E55) would return the 55th largest result which doesn't exist in this scenario.

jbkalla
Copper Contributor
Feb 13, 2023
Thank you!! You're a lifesaver! I didn't realize how that worked.