Forum Discussion

allodiba's avatar
allodiba
Copper Contributor
Jun 20, 2023

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

Thanks in advance!

excel
Formulas and Functions

2 Replies

  • PeterBartholomew1's avatar
    PeterBartholomew1
    Silver Contributor
    Jun 20, 2023

    allodiba 

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

  • OliverScheurich's avatar
    OliverScheurich
    Gold Contributor
    Jun 20, 2023

    allodiba 

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

Resources