Return row label for largest & 2nd largest value in criteria range

mcclosa 0
Viewed 35

I have the following table

Name Point Bonus Total Pos 1st Name 2nd Name
Bob 10 8 6 Point 11 10
Sue 9 5 3 Bonus 12 9
Joe 11 2 4 Total 10 7
Susan 7 9 10
Tim 1 12 4
Ellie 9 8 7

In G2 I have the following formula

{=LARGE(IF($B$1:$D$1 =$F2, $B:$D),1)}

Which returns the largest Point value, as 11.

In H2 I want to return the name where the Point value is 11. so the value for H2 should be Joe

Then in J2 want to do the same for the 2nd largest value. So the value of J2 should be Bob

excel-formula match indexing excel
1 Answers

I have used following formulas as per attached scheenshot.

G2=LARGE(INDEX($B$2:$D$7,,MATCH(F2,$B$1:$D$1,0)),1)
H2=INDEX($A$2:$A$7,MATCH(G2,INDEX($B$2:$D$7,,MATCH(F2,$B$1:$D$1,0)),0))
I2=LARGE(INDEX($B$2:$D$7,,MATCH(F2,$B$1:$D$1,0)),2)
J2=INDEX($A$2:$A$7,MATCH(I2,INDEX($B$2:$D$7,,MATCH(F2,$B$1:$D$1,0)),0))

And if you have Microsoft-365 then could try below formula to get names directly.

=LET(x,FILTER($B$2:$D$7,$B$1:$D$1=F2),FILTER($A$2:$A$7,x=LARGE(x,1)))

enter image description here

Harun24hr0
Related