This guide will explain how to use the **MAX function** on an **ArrayFormula** in Google Sheets.

The Array Formula allows you to perform calculations on multiple rows. With the MAX function, we can use the ARRAYFORMULA function to compare values in multiple rows using a single formula.

##### Table of Contents

Let’s take a look at a quick example of a situation where we might need to find the maximum value multiple times.

Suppose you have a list of products that you would like to purchase. For each of these products, you have kept track of their price in four different supermarkets.

You want to find out which stores have the highest markup price. Since you will need to find the maximum value for each row, we can use the `MAX`

function multiple times.

Alternatively, we may also create an Array Formula so that only one formula is used in the spreadsheet. This method is useful for performing this operation on a large dataset. Google Sheets will run much smoother if it only has to run one main formula to compute the column.

With the `DMAX`

, `TRANSPOSE`

, and `ARRAYFORMULA`

functions, we can create a single Array Formula that can return the maximum value per row. The `DMAX`

formula is often used for column-wise comparisons, but our data can be transposed to fit the input.

Let’s learn how to find the maximum values of several rows in Google Sheets and later test out the function with actual values.

**A Real Example of Using An ArrayFormula with MAX Function**

Let’s look at a real example of an Array Formula that finds the maximum value in a Google Sheets spreadsheet.

In this example, we are comparing product prices for a future project. We have ten unique products with prices we would like to compare. Using the Array Formula for finding the maximum value, we found out that the maximum price the first product was sold for was $21.00.

To get the values in Column C, we just need to use the following formula:

=ARRAYFORMULA(IFERROR( 1 / ( 1 / DMAX( TRANSPOSE(A2:E), SEQUENCE(ROWS(A2:E)), TRANSPOSE( IFERROR( COLUMN(A2:E) / 0)) ) ) ) )

The `DMAX`

function returns the largest number in a column of records in a list or database. Since we’re performing a row-wise search, we will have to use the `TRANSPOSE`

function on our dataset.

If any of these rows returns an error, we will have to catch the error with the `IFERROR`

function. The `IFERROR`

function will default to a null row. This is the reason why we divide the `COLUMN`

output by 0.

Finally, the `ARRAYFORMULA`

function indicates to Google Sheets that the formula passed should be done for each row in the range.

You can make your own copy of the spreadsheet above using the link attached below.

If you’re ready to try this ArrayFunction yourself in Google Sheets, follow our guide in the next section!

**How to Use MAX on ArrayFormula in Google Sheets**

This section will guide you through each step needed to start using an Array Formula to find the maximum value for each row in a range. The formula will allow the user to compute the maximum value for each row in a range without using the **Fill Handle** tool to copy a `MAX`

formula.

Follow these steps to start using the Array Formula method:

- First, add a new column to your table. This column will hold the contents of our Array Formula. In this example, we’ve added a new header under column F.

- Next, we must type the Array Formula method shown in the previous section. Make sure that the ranges referenced correspond to your sheet’s actual range of values.

- Hit the
**Enter key**to return the array output of the formula. In this example, we got the highest price for each product.

- If we need to add new columns, we simply need to adjust the range being referenced. If new rows are added, the Array Formula will automatically compute the maximum value for each added row.

**Frequently Asked Questions (FAQ)**

**What is the advantage of using the Array Formula?**

The Array Formula method is generally quicker to compute when dealing with a large number of entries. These functions are also future-proof. If the user decides to add more rows to a sheet, the`ARRAYFORMULA`

function will adjust dynamically to the new data. The typical method of dragging down the formula may be more convenient but also prone to errors.

That’s all you need to remember to start using the Array Formula in Google Sheets to find the maximum value. This step-by-step guide shows how we can use a single formula to find the highest price for every product in a table.

The `ARRAYFORMULA`

function is a powerful function in Google Sheets that works best when combined with other functions. With so many other Google Sheets functions out there, you can surely find one that suits your use case.

Are you interested in learning more about what Google Sheets can do?

Make sure to subscribe to our newsletter to be the first to know about the latest guides and tutorials from us.

## 1 comment

Hi, Can this arrayformula solution be changed from finding the MAX value of a range of columns to finding the MAX value of an array in one column? So, for example, in column BF, say, BF2:BF9 so that when the arrayformula spills down the column it is in, it returns the MAX value of, first, BF2:BF9, then, BF3:BF10, BF4:BF11, and so on? In other words, a “rolling” 8 period max for the last 8 periods?