# How to use max() with arrayformula()

This is my sheet :

in column E, i want to find the max among col A,B,C using this simple formula in E1 :

``````=max(A1:C1)
``````

But rather than pasting the formula to E2 .. E10, i want to see if possible to use `max()` in `arrayformula()` ? so i can use single formula for the whole column E.

### Formula

``````=BYROW(A:C, LAMBDA(r,
IFERROR(MAX(r)/(COUNTA(r)>0))))
``````
• Uses `COUNTA(r)>0` to return `1` if there are any values in the row, or `0` if the row only contains blank cells.
• Dividing by zero always returns an error which IFERROR handles by returning an empty value, which effectively skips blank rows.

If your data to compare is really only in three columns (A, B, C), you can simplify things with this formula in E1:

`=ArrayFormula(IF(A:A="",,IF((A:A>B:B)*(A:A>C:C),A:A,IF(B:B>C:C,B:B,C:C))))`

This reads "If any row of A is blank, return null; otherwise, if A is greater than B and A is greater than C, return A; otherwise, if B is greater than C, return B; otherwise, return C."

If you will have more columns than three and you know you will have fewer than 5500 rows of data and your numbers to compare will be similar in length to those shown in your post (i.e., numbers with 3 or fewer digits), then you can use this in E1:

`=TRANSPOSE(INDEX(QUERY(TRANSPOSE(FILTER(A:C,A:A<>"")),"SELECT "&JOIN(",","MAX(Col"&SEQUENCE(COUNTA(A:A),1)&")")),2))`

This essentially takes all non-null rows, `TRANSPOSE`s them, forms a `QUERY` that retrieves the `MAX` from as many columns as the original data set has rows, and then `TRANSPOSE`s everything back to the original orientation again.

The limitations are based on the fact that `JOIN` can only concatenated up to 50,000 characters. So if your real numbers may be up to 6 characters long, you can get about 4500 rows out of the above formula, etc.

The cleanest way to do this is with either byrow or bycol formulas. If you're looking for the max value of multiple rows, then if would look something like this:

=bycol(A1:C5,lambda(_c,max(_c)))

You can swap the logic to be byrow, and/or insert empty value logic into this as you need.

## The lambda way

The `max()` function is an aggregating function so it will not give row-by-row results in an array formula.

To iterate the data row by row, use `byrow()` with a lambda function that gets the max value on each row separately, like this:

``````=let(
data, A1:C,
rowMax_, lambda(r, iferror(1 / max(r) ^ -1)),
byrow(data, rowMax_)
)
``````

See let(), lambda(), byrow() and iferror().

## The old way

Before lambda functions became available in September 2022, row-by-row aggregation of data was often implemented with array formulas using the likes of `mmult()`, `sumif`, `countif()`, `countifs()` and `query()`, or "database" functions such as `daverage()`, `dcount()`, `dcounta()`, `dget()`, `dmax()`, `dmin()`, `dproduct()`, `dstdev()`, `dstdevp()`, `dsum()`, `dvar()` and `dvarp()`.

One pattern to get row-by-row max values is to apply `transpose()` and use `dmax()` with blank criteria created with `iferror()`, like this:

``````=arrayformula(iferror(
1 / dmax(
transpose({ row(A1:C), A1:C }),
row(A1:C),
iferror(A1:C / 0)
) ^ -1
))
``````

See dmax(), transpose(), row() and iferror().

• dmax() expects a header row, but a virtual header can be inserted as shown in the new formula. Commented Aug 22, 2023 at 8:12