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
CodePudding user response：
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)))
