If someone asks you how to find the Max value in each row in Google Sheets, you may think about using the Max and ArrayFormula combo.

You may think, in combination, it can return an array result.

But to your dismay, it can’t return an expanded array result of maximum values as seen on the image. In column H, you can see Max values in each row.

I want to apply a Max Array Formula in cell H3, which gives expanded maximum values in each row in column H.

In the above screenshot, I’ve applied a customized Max array formula in cell H3. I’ll come to that. But if you use a MAX formula like the one below to get an expanding array result, it won’t work as desired.

Note:- Please check your Sheet’s locale settings if the formulas don’t work.

Formula 1: This formula won’t return the Max value in Array in each Row

=max(B3:G6)

This formula will return the number 6 as it’s the maximum value in the range referred.

Now you may think you can use the above Max formula with ArrayFormula as below. But it’s not going to make any difference.

Formula 2: This formula is also not suitable to return Array result

=ArrayFormula(max(B3:G6))

The above two formulas are not ideal for us! Then how to find the maximum value in each row in Google Sheets? Which we are going to discuss in this tutorial.

How to Find Max Value in Each Row in Google Sheets

You can find below the formulas to use in cell H3 as per the above example.

Formula 3: Master Formula to find max value in each row in Google Sheets

=query(
     transpose(
        query(
           transpose(A3:G),
           "Select "&REGEXREPLACE(join("",ArrayFormula(if(len(A3:A),
           "Max(Col"&ROW(A3:A)-ROW(A3)+1&"),",""))), ".\z","")&""
        )
     ),"Select Col2"
)

This formula is one of the best formulas to use as an alternative to the Max formula in an expanding Array result. Additionally, it can handle infinite ranges like A3:G instead of A3:G6.

You only need to apply this formula in cell H3. It will take care of all the rows in the range A3:G and return maximum values in each row.

Very Important: A Much Cleaner New BYROW Solution to Find Max in Each Row Added at the End

Can I Use this formula in my Sheet to find the maximum value in each row? If yes, then what changes should I make?

You can use this formula to find the Max value in each Row in Google Sheets. But please follow the instructions below.

In concise the formula works as below.

1. Wherever fruit names appear in column A, the formula may add max values in Column H in that row. That means it is for infinite ranges.

2. Regarding columns, you can delete any column other than columns A and H. Of course, in H3, we have our formula. But needless to say, there should be at least one column left between A and H to find the Max value.

3. Similarly, you can insert any number of columns between columns A and H. The formula can automatically adjust the columns.

4. If you insert a new column after G, before H, please change the cell reference in the formula accordingly.

That means you can use the above single formula in your sheet and find maximum values in each row.

You May Also Like: Remove Duplicate Rows and Keep The Rows With Max Value

Formula Explanation – for those who wish to learn new things in Google Sheets

Let me explain how the formula works to find the Max value in each Row in Google Sheets.

As I’ve told you at the beginning of this Google Sheets tutorial, if the Max function or Large function supports expanded array results, there is no point in using this formula. They are not. So as an alternative, I have used the Query function here.

This workaround uses Google Sheets Query Function and its “Select” clause with Max.

You can use the Google Sheets Query function to find the Max value in multiple columns at a time.

Yep! in columns, not rows. Here we want to find Max Value in each row in Google Sheets, not in each column.

That means if our data is column-wise, we can find the Max value using Query. Then there is a solution! Just transpose our above data and make it column-wise.

We can use the function Transpose to change the data orientation from row to column.

Cell range A3:G is the required data for our calculation. See the same after transposing.

Now we want to find the max value in each column, not in rows. So you can use a Query formula as below.

Formula 4: Find Max Value in Columns

=query(transpose(A3:G),"Select Max(Col1), Max(Col2), Max(Col3),Max(Col4)")

Result:

This formula can return the max values in each column as above. But the Query formula puts labels for maximum values in each column.

With the help of another Query formula, you can remove that unwanted labels. First, transpose this result for that we can apply another Query. See that formula.

Formula 5: Base formula to find Max Value in Rows

=query(transpose(query(transpose(A3:G),"Select Max(Col1), Max(Col2), Max(Col3),Max(Col4)")),"Select Col2")

You can use this formula directly in Cell H3 to find the Max value in each Row in Google Sheets. It’s an array formula with expanding results.

Why is this formula different from our master formula # 3 above?

The reason, unlike our master formula, this formula is not flexible. It works only for the current range B3:G6.

If you add a new fruit in A7 and its price in that row, this formula can’t find the max value for that row. My master formula is capable of doing this. See that master formula once again (Formula # 3).

If you closely compare the above formula # 5 (base formula) with the master formula # 3, you can see some key differences. It’s in the “Select” clause in the Query.

The “Select” clause that starts with Max in the base formula is replaced by a few combination formulas in the final formula.

The below formula part in the base formula is,

"Select Max(Col1), Max(Col2), Max(Col3),Max(Col4)"))

replaced by this combination formula part in the master formula.

"Select "&REGEXREPLACE(join("",ArrayFormula(if(len(A3:A),"Max(Col"&ROW(A3:A)-ROW(A3)+1&"),",""))), ".\z","")&""))

The combination formula returns the same “Select” clause but makes the formula dynamic. How?

We have the product names (fruits) in the range A3:A6. There are four fruits, and we want the Max value of these four rows.

We can use the Max function for only one row. That means if you apply one max formula in H3, additional formulas are required for H4, H5, and so on. If there are a large number of rows, it’s not an ideal choice.

As already told, we can’t use Query either in rows to find the Maximum value. But we can use Query in columns to find Max.

So I’ve transposed the data and used the Query formula. I’ve already explained this with the screenshot above. But here, I am trying to explain a different thing.

When you transpose the data, there are four columns. Because we have four rows with fruit names, and those rows became columns. So if you count the row numbers, you can say use that many columns in the Query formula.

In an earlier tutorial, using the ROW function, I detailed how to automate serial numbering in Google Sheets. The same technique I am adopting here. The below formula can return serial numbers 1, 2, 3, and 4.

=ArrayFormula(ROW(A3:A6)-ROW(A3)+1)

But if more fruit items are there in Column A, it should return serial numbers accordingly. For example, for five fruits, it should return 1, 2, 3, 4, and 5. Five means five rows, and after transposing it for Query use, we can say five columns.

So I have used infinite range as below for the counting. In this formula, the LEN function controls blank rows.

=ArrayFormula(if(len(A3:A),ROW(A3:A)-ROW(A3)+1,""))

I’ve used it in the final formula with some more addition.

Some more addition?

Yup! The above formula returns serial number 1 for item description 1 (Apple), 2 for item description 2 (Orange), and so on.

Once transposed, these row numbers act as column numbers. In Query, we want to use the “Select” clause as below.

Max(Col1), Max(Col2)...

We can convert the row numbers (it’s column numbers actually) with the help of the ampersand sign as explained below.

Similar: Difference Between JOIN, TEXTJOIN, CONCATENATE Functions in Google Sheets

I’ve put the below text before the serial number.

Max(Col

And the following text after the serial number.

"),"

The formula and the result are as follows.

=ArrayFormula(if(len(A3:A),"Max(Col"&ROW(A3:A)-ROW(A3)+1&")",""))

Result:

Max(Col1)
Max(Col2)
Max(Col3)
Max(Col4)

As you can see, the result is in multiple rows. To use it in Query, we want this as a combined text string as below.

Max(Col1), Max(Col2), Max(Col3),Max(Col4)

So I’ve used the JOIN function to join these texts and put the comma as the delimiter for joining. It causes one new issue.

What’s that?

At the end of the joined text, there would be an additional comma. I’ve removed that with Regexreplace in the formula. That’s the making of a perfect array formula to find max values in each row.

Important Update

While I was going through my tutorial, I realized that I could exclude the REGEXREPLACE function and fine-tune the formula. For that, I should replace the JOIN function with the TEXTJOIN function.

You can use the final formula as below, which is easier to understand.

=query(
     transpose(
        query(
           transpose(A3:G),
           "Select "&textjoin(",",TRUE,ArrayFormula(if(len(A3:A),
           "Max(Col"&ROW(A3:A)-ROW(A3)+1&")","")))&""
        )
     ),"Select Col2"
)

I think this formula is the possibly best formula to find the Max value in each Row in Google Sheets. Please leave your views in the comments.

Note:- If you have hundreds of rows filled with data, the above formula may fail because of the Textjoin and Ampersand restrictions.

MAX in Every Row Using BYROW Lambda Helper Function

Here is the BYROW to get the MAX value in every row in Google Sheets.

=byrow(B3:G,lambda(mv,if(count(mv)=0,,max(mv))))

Insert this BYROW formula in cell H3. It will spill down provided H4:H is blank.

In this formula, we should feed the entire data array/range. So I have used B3:G instead of B3:G3.

If your table contains a text column, as per my sample data, you can include or exclude that.

But even if you include that, I mean A3:G instead of B3:G, the formula won’t fail.

Now I’ll come to the lambda part, and let’s start with the name mv, which is an identifier that represents a single row in the array.

If the count of values in mv is 0, the formula returns blank; else, the max value in that row.