CODEWITHSUNDEEP
excelindexingmatch
user1856146
user1856146

Reputation: 3

Find highest 3 values in a row and return corresponding column headers in Excel

In MS Excel, I am trying to identify the highest 3 values within a row and match the relevant corresponding header value. However, when the values are the same it returns the same header value. However, I would like it to return the next header value.

enter image description here

Upvotes: 0

Views: 920

Answers (1)

Dirk Reichel
Dirk Reichel

Reputation: 7979

assuming the label "Largest" to be at A2 put in cells

A3:  =IFERROR(LARGE(G3:M3,1),"")
B3:  =IF(A3="","",INDEX($1:$1,1,LARGE((G3:M3=A3)*COLUMN(G3:M3),1)))
C3:  =IFERROR(LARGE(G3:M3,2),"")
D3:  =IF(C3="","",INDEX($1:$1,1,LARGE((G3:M3=C3)*COLUMN(G3:M3),1+(A3=C3))))
E3:  =IFERROR(LARGE(G3:M3,3),"")
F3:  =IF(E3="","",INDEX($1:$1,1,LARGE((G3:M3=E3)*COLUMN(G3:M3),1+(A3=E3)+(C3=E3))))

Cells B3, D3 and F3 are array formulas and must be confirmed with ctrl+shift+enter

Upvotes: 1

Related Questions