How to Use the MAX Function in Google Sheets

Published on

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

Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Hierarchical Numbering Sequences in Excel

Creating hierarchical numbering sequences in an Excel spreadsheet can significantly improve the way you...

How to Easily Repeat a Sequence of Numbers in Excel

Excel offers multiple ways to accomplish tasks, and the simplicity of each approach depends...

Create a Sequence of Dates at Every Nth Row in Excel (Dynamic Array)

Would you like to create a sequence of dates in every nth row in...

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

More like this

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

Limit Formula Expansion to a Specific Row in Google Sheets

In this tutorial, I’ll explain how to limit the expansion of an array formula...

3-D Referencing Structured Data Tables in Google Sheets

When you have several tables within a single sheet—not across multiple sheets in a...

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.