Forum Discussion
Excel 2016 INDEX MATCH fixing duplicate entries
=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)
=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.
- Thank you!! You're a lifesaver! I didn't realize how that worked.
