Find here all that you want to know about Vlookup in Max Rows in Google Sheets. You can tune the Vlookup to return only values from Max Rows in Google Sheets.

Lots of spreadsheet-related tasks require Vlookup in one way or another. So learning this function in its all possible variations will give you an upper hand in data manipulation.

Let us come back to our topic, i.e., Vlookup in Max Rows in Google Sheets. I am considering the below points in this Google Sheets tutorial.

1. Find max value in a column and return value from the next column.
2. Lookup a search key (possibly a string) and return the max value or value from the max row. That means the search key will be in the table multiple times. We only want the max value or any other value from the max value row of this key.
3. How to use multiple search keys and return multiple max values or multiple values from the max row?

In concise, this tutorial is all about Max in Vlookup in Google Sheets. So here we go.

All About Vlookup in Max Rows in Google Sheets

Learn how to use Vlookup to return only values from the max rows in Google Sheets. You can learn this Vlookup Max concept under different subcategories below.

Find Max Value and Return Value from the Next | Adjoining Column

For this type of lookup, you can use the MAX function with Vlookup. Instead of feeding a search key directly in Vlookup, let the MAX return the max value as the search key.

=vlookup(max(A2:A9),A2:B9,2,0)

In most cases, your column that contains the max value will be second, third, or fourth.

If so, you can flip the columns using the ArrayFormula in Vlookup. As you may know, the Vlookup in Sheets can only search down the first column for the key.

Assume in your Sheets, the name column is the first column, and the Score column is the second. Then use the formula as below.

=ArrayFormula(vlookup(max(B2:B9),{B2:B9,A2:A9},2,0))

So you have just learned the basics of using MAX in Vlookup in Google Sheets.

We are discussing how to use Vlookup in Max Rows in Google Sheets. Here is the second tip.

You May Like: Find Min, Max in a Matrix and Return a Value from the Same Row.

Lookup a Search Key and Return the Max Value or Value from the Max Value Row

Here the search key is not the max value.

This time you have the search key (text string) to search down the first column.

But the first column may or may not contain the search key multiple times.

How to return the max value or any other value of the search key from the row that contains the max value?

=vlookup("Student 2",sort(A2:B9,2,false),2,false)

I have used the SORT function with Vlookup here.

Without using SORT, the Vlookup formula would return the value 550 that is in cell B5. Because the search key “Student 2” is first appearing in that row.

I have sorted the data range in descending order based on column 2.

It puts the max values on the top. So, the max value row # 6 will be on top of the current row #5.

Here also you can flip the columns in Vlookup as we have done in example # 1 above.

Here is another notable point.

If you want to look up a search key and return value from any column from the Max row, you only need to change the column index in the formula.

For example, I have a three-column dataset that contains;

Student names in the first column, scores in the second column, and random dates in the third column.

You can return the date corresponding to the max score of the search key as below.

=vlookup("Student 2",sort(A2:B9,2,false),3,false)

Now see one advanced Max Value in Vlookup tip. Here I am following the above Vlookup max example.

The only difference is in the use of multiple search keys.

Vlookup to Only Return Values from Max Rows in Sheets (Multiple Search Keys)

See this example. You can use the just above formula with some changes.

What are those changes?

E.g.:-

=ArrayFormula(vlookup(E2:E4,sort(A2:C10,2,false),2,false))

This formula may look complex, but it is simple to understand.

Multiple Max in Vlookup – Formula Logic

Here we have the Vlookup search keys in the range E2:E4, and that is the student names.

What we want is their max scores in Column B.

As we have done earlier, sort the ‘Score 1’ column B in descending order within the Vlookup.

Use the search key as a range E2:E4 instead of “Student 2” in the earlier formula.

Further, use the ArrayFormula as we want an array result.

Vlookup Alternative to Return Values from Max Rows (Using SORT + SORTN)

In Vlookup we have used the SORT to sort the ‘Score 1’ column in descending order. It places the higher scores on the top.

Now take a look at column A.

If you can remove the duplicates, I mean the second and third occurrences of the same student names, then the three student names and their max scores will only be left.

In this workaround, I have wrapped that SORT formula with the SORTN as below.

=sortn(sort(A2:C10,2,false),9^9,2,1,true)

Note:- Unlike Vlookup, there is no need to specify the criteria separately.

The SORTN removes the duplicates based on the Student names. Want to know the parameters used in SORTN above.

• 9^9 – You can replace this with 1000 or any large number as its role is ‘n’ (infinite).
• 2 = it’s the display_ties_mode in SORTN.
• 1 – sort column (the column to unique).
• true – sort order.

Must Read: Remove Duplicates in Google Sheets [The Complete Guide and Sample Sheet]

I mean, the SORTN + SORT combo alone would return the below result, and that’s the max rows! This tip is entirely new to you, right?

That’s all about Max in Vlookup in Google Sheets. Enjoy!