# How can I avoid duplicate values being returned with INDEX MATCH?

Copper Contributor

# How can I avoid duplicate values being returned with INDEX MATCH?

I'm trying to return the top 10 worst percentages with a LARGE function, and then in an adjacent column, use the INDEX MATCH functions to return which question corresponds with the % value. The problem is several questions have the same % value. What must I add to the function to ignore previously pulled values (questions)?

2 Replies

# Re: How can I avoid duplicate values being returned with INDEX MATCH?

``=INDEX(\$A\$1:\$A\$27,MATCH(1,(\$B\$1:\$B\$27=F2)*(\$C\$1:\$C\$27=E2),0))``

You can try this formula along with helper columns. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel 2021.

``=COUNTIF(\$B\$1:B1,B1)``

This is the formula in cell C1 in the example.

In column F is the LARGE function and column E is another COUNTIF which counts the values of column F.

# Re: How can I avoid duplicate values being returned with INDEX MATCH?

This uses 365.  If one sets out to exploit 365 to the full, the resulting formulas tend to be unrecognisable to anyone versed in traditional spreadsheet methods.  In this case

``= TAKE(SORTBY(table, percentage, -1), 10)``

That is: sort the entire table by the percentage column descending and select the first 10 rows.  For me 365 was worth the investment but then, I was delighted to dispense with concepts such as direct cell referencing (ignoring the 17 billion predefined names)  and relative referencing (used to build lists).