I have columns with Names, Ages and Points in columns A-C. Based on the age, I'm able to return the highest points for each age. I'm having trouble then returning the name of that person with that highest points score in each age group.
Currently, I've tried:
=IF(MATCH(F$2,$B$3:$B$20),INDEX($A$3:$A$20,MATCH(F$3,$C$3:$C$20,0)))
but this doesn't work when there's another name with the same points, even if from a different age.
Any help would be very much appreciated.
Currently, I've tried:
=IF(MATCH(F$2,$B$3:$B$20),INDEX($A$3:$A$20,MATCH(F$3,$C$3:$C$20,0)))
but this doesn't work when there's another name with the same points, even if from a different age.
Any help would be very much appreciated.
| Book3 | ||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | |||
| 1 | Ages | |||||||||||
| 2 | Name | Age | Points | 6 | 7 | 8 | 9 | 10 | ||||
| 3 | Brian | 7 | 56 | Highest Points | 90 | 86 | 65 | 86 | 56 | |||
| 4 | Bill | 8 | 65 | Name | Rosie | Scott | Bill | Scott | Brian | |||
| 5 | Joe | 7 | 74 | |||||||||
| 6 | Henry | 9 | 64 | 2nd Highest | 56 | 74 | 60 | 78 | ||||
| 7 | Adam | 10 | 56 | Name | ||||||||
| 8 | Scott | 7 | 86 | |||||||||
| 9 | Parker | 8 | 46 | |||||||||
| 10 | Kevin | 9 | 78 | |||||||||
| 11 | Rosie | 6 | 90 | |||||||||
| 12 | Sarah | 8 | 60 | |||||||||
| 13 | Natalie | 7 | 65 | |||||||||
| 14 | Alice | 9 | 86 | |||||||||
| 15 | Kerry | 6 | 56 | |||||||||
| 16 | John | 8 | 45 | |||||||||
| 17 | Emma | 7 | 34 | |||||||||
| 18 | Julie | 9 | 43 | |||||||||
| 19 | Kate | 9 | 54 | |||||||||
| 20 | Wayne | 7 | 68 | |||||||||
Sheet1 | ||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| F3:J3 | F3 | =IFERROR(LARGE(IF($B$3:$B$20=F$2,$C$3:$C$20),1),"") |
| F4:J4 | F4 | =IF(MATCH(F$2,$B$3:$B$20),INDEX($A$3:$A$20,MATCH(F$3,$C$3:$C$20,0))) |
| F6:J6 | F6 | =IFERROR(LARGE(IF($B$3:$B$20=F$2,$C$3:$C$20),2),"") |
| Press CTRL+SHIFT+ENTER to enter array formulas. | ||
