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$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 Thanks, I got my worksheet working with this also. But could you please explain what the COUTIF is counting in this formula? I couldn't find any examples with an array as criteria.
- Thank you! That worked! I'll have to test it in Excel 2016, but it works in 365.
