2

Let's say I have a sheet of student data.

  • column A: grade level
  • column B: student name
  • [not relevant: columns C - G: absences, parents' phone number, credits completed, etc]
  • column H: GPA

I want to find the highest GPA in each grade level and return the GPA and the name of the student associated with that GPA. How would I do that in Google Sheets?

I know how to return the highest GPA by grade level using a query but I don't know how to return a value from another column (name) along with it.

1

To get Max_GPA against a GradeLevel, I used formula from post which i have commented in this thread.

To get the name corresponding to Max_GPA, I am using a VLOOKUP. Points to note while using Vloopup: 1) Ensure the left most column should contain values which we are looking for (in the screenshot - E2). Thus GPA has to be the first column.

Image

Since VLOOKUP comes with a limitation of placing GPA values in the first column, the better approach is to use a INDEX and MATCH function together. Image2 Source : http://www.mbaexcel.com/excel/why-index-match-is-better-than-vlookup/

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.