SOLVED

# Excel 2016 INDEX MATCH fixing duplicate entries

Copper 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))

9 Replies
best response confirmed by jbkalla (Copper Contributor)
Solution

# Re: 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.

# Re: Excel 2016 INDEX MATCH fixing duplicate entries

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)))``

# Re: Excel 2016 INDEX MATCH fixing duplicate entries

@jbkalla Perhaps like this?

# Re: Excel 2016 INDEX MATCH fixing duplicate entries

Thank you! That worked! I'll have to test it in Excel 2016, but it works in 365.

# Re: Excel 2016 INDEX MATCH fixing duplicate entries

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

Is there something about the formula that requires the cells to start at the top?

@Jan Karel Pieterse:  I'm unable to sort the original columns, so I'm guessing your solution won't work for my case.  But thank you!

# Re: Excel 2016 INDEX MATCH fixing duplicate entries

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

# Re: Excel 2016 INDEX MATCH fixing duplicate entries

Thank you!! You're a lifesaver! I didn't realize how that worked.

# Re: Excel 2016 INDEX MATCH fixing duplicate entries

@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)

# Re: Excel 2016 INDEX MATCH fixing duplicate entries

@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.
1 best response

Accepted Solutions
best response confirmed by jbkalla (Copper Contributor)
Solution

# Re: 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.