Tip Printed from ExcelTip.com

---

Returning the Entire Contents of the Row Containing the Highest Math Grade


Problem:

Each row of columns B:E contains data on a student's ID, year, subject, and grade.
We want to find the highest math mark in the data range and retrieve all the information from that particular row.

Solution:

Use the INDEX, MATCH, and MAX functions as shown in the following Array formula:
{=INDEX(B2:B6,MATCH(MAX(($E$2:$E$6)*($D$2:$D$6="Math")),$E$2:$E$6,0))}
Copy the formula from cell B8 to cells C8:E8.