Forum Discussion

griffinursin's avatar
griffinursin
Copper Contributor
Mar 30, 2022

Index+Match+Large Function returning Duplicate Values

Hello:   Current Formula: "Donates to "&INDEX($B$1:$K$1,MATCH(LARGE(B2:K2,1),B2:K2,0))&", "&INDEX($B$1:$K$1,MATCH(LARGE(B2:K2,2),B2:K2,0))&", and "&INDEX($B$1:$K$1,MATCH(LARGE(B2:K2,3),B2:K2,0))&" ...
excel
Formulas and Functions
mtarler's avatar
mtarler
Silver Contributor
Mar 30, 2022

griffinursin assuming you have Office365 i suggest the following formula to get the top 3:

="Donates to "&TEXTJOIN(", ",TRUE,INDEX(SORT($B$1:$K2,ROW(),-1,TRUE),1,{1,2,3}))&" related causes."

 

  • mtarler's avatar
    mtarler
    Silver Contributor
    Mar 30, 2022

    here is another option:

    ="Donates to "&TEXTJOIN(", ",TRUE,FILTER($B$1:$K$1,$B2:$K2>=LARGE($B2:$K2,3),"no"))&" related causes."

    the advantage of this option is if #3 is a tie then this option will include those others also but this one is not sorted in order of greatest to least in the output list. 

Resources