Forum Discussion
jbkalla
Feb 13, 2023Copper Contributor
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.
- jbkallaCopper Contributor
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!- JKPieterseSilver Contributor
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)
- OliverScheurichGold Contributor
=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.
- jbkallaCopper ContributorThank you!! You're a lifesaver! I didn't realize how that worked.
- JKPieterseSilver Contributor
- Patrick2788Silver Contributor
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)))
- OliverScheurichGold Contributor
=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.
- MiiluCopper ContributorOliverScheurich 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.
- jbkallaCopper ContributorThank you! That worked! I'll have to test it in Excel 2016, but it works in 365.