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.
=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.