johnsnider
New Member
- Joined
- Aug 23, 2018
- Messages
- 9
A | B | C | |
1 | ID | Subject | score |
2 | 1000 | Math | 80 |
3 | 1001 | English | 45 |
4 | 1002 | Math | 60 |
5 | 1002 | English | 87 |
6 | 1001 | English | 50 |
7 | 1000 | Chem | 54 |
8 | 1001 | Math | 71 |
9 | 1002 | Chem | 83 |
10 | |||
11 | |||
12 | |||
13 | ID | Name | Age |
14 | 1000 | matt | 12 |
15 | 1001 | john | 15 |
16 | 1002 | simon | 16 |
<tbody>
</tbody>
This solution needs to be a formula that must be within one cell and needs to be dynamic(e.g changes).
Q
What subject did john score the highest?
So far I've attempted this but I don't understand why it's not working
=INDEX(B2:C9,MATCH(MAX(IF(A2:A9=INDEX(A14:A16,MATCH("john",B14:B16,0)),C2:C9)),C2:C9,0),1)