Forum Discussion
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 Request"). How can I get around that in Excel 2016?
Cell G2: =LARGE($B$2:$B$10,E2)
Cell F2: =INDEX($A$2:$A$10, MATCH(G2,$B$2:$B$10,0))
=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.
Download 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.
jbkalla Perhaps like this?
An INDEX-SMALL array can do this.
Press ctrl+shift+enter to define as an array in Excel 2016
=INDEX($A$1:$A$10,SMALL(IF($B$1:$B$10=J2,ROW($B$1:$B$10)),COUNTIF($J$2:J2,J2)))=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.
