Forum Discussion
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...
=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.
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!
jbkalla Not sure what you mean by "Unable to sort the original columns", my formulas do not require the source data to be sorted? (although sorting may affect how ties are displayed in the list)