Index match min if

[johnsnider]

	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)

 

[Marcelo Branco]

Try this array formula

=INDEX(B2:C9,MATCH(MAX(IF(A2:A9=INDEX(A14:A16,MATCH("john",B14:B16,0)),C2:C9)),IF(A2:A9=INDEX(A14:A16,MATCH("john",B14:B16,0)),C2:C9),0),1)
confirmed with Ctrl+Shift+Enter, not just Enter

M.

 

[johnsnider]

Very nice it worked, thank you for the help. Could I possibly get your logic behind the second if statement or why you used 2 IFs.

 

[Marcelo Branco]

The second argument of MATCH, lookup_array, should contain only the values corresponding to John, otherwise the formula may return an incorrect value.

For example, change the value of the cell C5 from 87 to 71 (identical to John's maximum). A formula that uses simply C2:C9 as the second argument of MATCH (lookup_array) would return English which obviously is wrong.

M.

 

[johnsnider]

Thank you, Marcelo I appreciate the explanation. I see now why my attempt failed.

 

[Marcelo Branco]

You are welcome. Glad to help.

M.