Google-sheets – How to return a name alongside certain values in Google Sheets

google sheetsgoogle-sheets-query

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.

Best Answer

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/

Related Topic