How can VLOOKUP be used to return the maximum value in Google Sheets?

By /

VLOOKUP is a function in Google Sheets that allows users to search for a specific value in a table and return a corresponding value from a different column. To return the maximum value using VLOOKUP, the function can be used in combination with the MAX function, which calculates the highest value in a given range. By specifying the range of values to search in and the column to return the value from, VLOOKUP can find the highest value and return it as the result. This makes it a useful tool for quickly finding the maximum value in a dataset in Google Sheets.

You can use the following syntax in Google Sheets with the MAX and VLOOKUP functions to find the max value in a range and return a corresponding value:

=VLOOKUP(MAX(A2:A11), A2:B11, 2, FALSE)

This particular formula uses MAX to find the max value in the range A2:A11 and then uses VLOOKUP to look up the value in the range B2:B11 that corresponds to this max value.

The following example shows how to use this formula in practice.

Example: How to Use VLOOKUP with MAX in Google Sheets

Suppose we have the following dataset that contains information about points scored by various basketball players:

Suppose we would like to look up the max value in the points column and return the corresponding team name.

We can type the following formula into cell D2 to do so:

=VLOOKUP(MAX(A2:A11), A2:B11, 2, FALSE)

The following screenshot shows how to use this formula in practice:

This formula uses the MAX function to find the max value of 40 in the points column.

Then, the formula uses the VLOOKUP function to return the team name Warriors, which is the team that corresponds to the max value in the points column.

Bonus: Find the Max Value Associated with a Lookup Value

If you instead wanted to find the max value associated with a specific lookup value, you could use the MAXIFS function.

For example, you could use the following formula to find the max value associated with the “Warriors” team:

=MAXIFS(B2:B9, A2:A9, "Warriors")

The following screenshot shows how to use this formula in practice:

The formula returns a value of 30, which is the max value associated with the “Warriors” in this dataset.

Additional Resources

The following tutorials explain how to perform other common tasks in Google Sheets:

x

Related terms:

  1. How can I use VLOOKUP in Google Sheets to return the last matching value?
  2. How can VLOOKUP be used in Google Sheets to return the minimum value?
  3. How Can INDEX MATCH Be Used to Return the Maximum Value in Excel?
  4. Can I use VLOOKUP to return multiple columns in Google Sheets?
  5. How to Use VLOOKUP to Return Value in Next Cell in Excel?
  6. How Can I Use VLOOKUP in Google Sheets to Find a Value That Falls Within a Range?
  7. What is the process for setting minimum and maximum values in formulas on Google Sheets?
  8. How do I use VLOOKUP to Return Blank Instead of #N/A in Excel?
  9. How can I use VLOOKUP to return blank instead of zero in Excel?
  10. Excel: How to use VLOOKUP to return multiple columns?