There are several ways to find the maximum value in a range in Google Sheets, and the MAX function is one of them. Other methods include using SORTN or QUERY to sort the data in descending order and return the first value. Additionally, you can use the LARGE function. In this tutorial, we will focus on the MAX function, which is the simplest option.

Example:

=MAX(D1:E10)

This formula returns the maximum value in the range D1:E10.

Syntax:

MAX(value1, [value2, …])

Arguments:

• value1: The first value, cell reference, array constant, or cell range to consider when calculating the maximum value.
• value2: Optional. The second value, cell reference, array constant, or cell range to consider when calculating the maximum value.

Examples:

• =MAX(50, 100) — where 50 is value1 and 100 is value2.
  Returns: 100
• =MAX({100, 200, 50, 350}) — where {100, 200, 50, 350} is value1.
  Returns: 350
• =MAX(A1:A) — where A1:A is value1.
  Returns: The maximum value in column A
• =MAX(D1:D7, E1:E11) — where D1:D7 is value1 and E1:E7 is value2.
  Returns: The maximum value in the combined ranges D1:D7 and and E1:E11.

These examples demonstrate how to use the MAX function in Google Sheets.

Combining MAX with Other Functions for Advanced Analysis

The MAX function is commonly used with lookup functions such as XLOOKUP and FILTER.

For example, to find the item with the highest price in a list, you can combine XLOOKUP with MAX.

Assume column A contains item names and column B contains their prices.

You can use the following formula:

=XLOOKUP(MAX(B1:B), B1:B, A1:A)

In this formula, the MAX function acts as the search key. XLOOKUP searches for this key in column B and returns the corresponding value from column A.

Syntax:

XLOOKUP(search_key, lookup_range, result_range)

However, if there is more than one item with the same maximum value, you can use FILTER instead of XLOOKUP to return all matching items:

=FILTER(A1:A, B1:B=MAX(B1:B))

This FILTER function returns the values in column A that meet the condition B1:B=MAX(B1:B).

Syntax:

FILTER(range, condition1, [condition2, …])

Similarly, you can use the MAX function with other functions such as VLOOKUP, LOOKUP, HLOOKUP, MATCH, XMATCH, INDEX, etc.

For example, the following formula uses XMATCH to find the relative position of the maximum value in column B, and INDEX to return the corresponding value from column A:

=INDEX(A1:A, XMATCH(MAX(B:B), B:B))

Additional Tips

• The MAX function includes values from hidden rows when the value1 argument is a range. To exclude hidden rows, consider using the SUBTOTAL function instead.
• If the specified cell or range is empty, the MAX function will return 0. To return an empty cell instead of 0, use this formula:
  =LET(maximum, MAX(B1:B), IF(maximum=0,,maximum))
  Replace B1:B with the range where you want to find the maximum value.
• According to the official documentation, the MAX function is specified to handle up to 30 arguments. However, Google Sheets supports an arbitrary number of arguments for this function.

Resources

• How to Find Max Value in Each Row in Google Sheets
• How to Highlight Max Value in a Row in Google Sheets
• Filter Menu to Filter Max N Values in Google Sheets
• Highlighting the Max Values in Each Group
• Hyperlink Max and Min Values in Column or Row in Google Sheets
• Column Header of Max Value in Google Sheets Using Array Formula
• Get Max Date in Each Row Using an Array Formula in Google Sheets
• Max and Min Strings Based on Alphabetic Order in Google Sheets
• Running Max Values in Google Sheets (Array Formula Included)