If you’ve ever tried to find the maximum value in each row in Google Sheets, you may have thought to use the Max and ArrayFormula combo. However, you quickly discovered that it doesn’t return an expanded array result of maximum values as expected. But don’t worry, I’m here to spill the beans on an alternative solution that works like magic!
The Problem with Max and ArrayFormula
Let’s take a look at the example below. In column H, you can see the Max values in each row.
If you try using the following formula, you’ll end up with just the maximum value in the range referred:
=max(B3:G6)And even if you use the ArrayFormula with the Max formula, it won’t make any difference:
=ArrayFormula(max(B3:G6))Clearly, these formulas are not ideal for our purpose. So how do we find the maximum value in each row in Google Sheets? Let’s find out!
The Master Formula
Behold, the master formula to find the max value in each row in Google Sheets:
=query( transpose( query( transpose(A3:G), "Select "®EXREPLACE(join("",ArrayFormula(if(len(A3:A), "Max(Col"&ROW(A3:A)-ROW(A3)+1&"),",""))), ".z","")&"" ) ),"Select Col2" )This formula is a game-changer! It not only handles infinite ranges but also returns the maximum values in each row. Simply apply this formula in cell H3, and it will take care of all the rows in the range A3:G.
A Cleaner Solution
But wait, there’s more! We have an even cleaner solution to find the maximum value in each row. Introducing the BYROW Lambda Helper Function:
=byrow(B3:G,lambda(mv,if(count(mv)=0,,max(mv))))Insert this BYROW formula in cell H3, and it will spill down to all the rows where column H is blank. You can even include or exclude text columns in your table, and the formula will still work like a charm.
Conclusion
There you have it – the secrets to finding the maximum value in each row in Google Sheets! Whether you choose the master formula or the BYROW Lambda Helper Function, you can say goodbye to the limitations of the Max and ArrayFormula combo. So go ahead, give it a try, and let us know your thoughts in the comments.
