Index match min if

[johnsnider]

	A	B	C
Math
English
Math
English
English
Chem
Math
Chem
matt
john
simon

<tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C0C0C0]#C0C0C0[/URL] , align: center"]ID[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C0C0C0]#C0C0C0[/URL] "]Subject[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C0C0C0]#C0C0C0[/URL] , align: center"]score[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]2[/TD]
[TD="align: center"]1000[/TD]

[TD="align: center"]80[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]3[/TD]
[TD="align: center"]1001[/TD]

[TD="align: center"]45[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]4[/TD]
[TD="align: center"]1002[/TD]

[TD="align: center"]60[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]5[/TD]
[TD="align: center"]1002[/TD]

[TD="align: center"]87[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]6[/TD]
[TD="align: center"]1001[/TD]

[TD="align: center"]50[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]7[/TD]
[TD="align: center"]1000[/TD]

[TD="align: center"]54[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]8[/TD]
[TD="align: center"]1001[/TD]

[TD="align: center"]71[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]9[/TD]
[TD="align: center"]1002[/TD]

[TD="align: center"]83[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]10[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]11[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]12[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]13[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C0C0C0]#C0C0C0[/URL] , align: center"]ID[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C0C0C0]#C0C0C0[/URL] "]Name[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C0C0C0]#C0C0C0[/URL] , align: center"]Age[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]14[/TD]
[TD="align: center"]1000[/TD]

[TD="align: center"]12[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]15[/TD]
[TD="align: center"]1001[/TD]

[TD="align: center"]15[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]16[/TD]
[TD="align: center"]1002[/TD]

[TD="align: center"]16[/TD]

</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.