We use the MIN function to find and return the Minimum value in a range that contains numeric values. It’s an aggregate function that only returns a single value. But you can use MIN in Array in Google Sheets in a particular way, or we can say a customized way, to get expanded results.

It can return the minimum values in each row with a single piece of Array Formula.

Example

I have different products in each row and their prices from various sellers in multiple columns. I want to find out the lowest price of each product.

Of course, we can use the MIN function in cells H3, H4, and so on, which means a copy-paste formula.

In that method, you may need to apply the below MIN formula in cell H3 and copy-paste it down the rows.

=min(B3:G3)

It’s not advisable. Why?

If your data has hundreds of rows, this formula is in no way worthy. It will be a tedious job for you to copy and paste this formula into the entire row.

The formula may even break when you insert new rows.

As mentioned above, I have a customized type of MIN array formula based on QUERY for you. Please find that formula under the subtitle below (Formula 1).

A Very Important Update: We can now use the BYROW function to expand MIN quite simply. You may find that solution in a later part of this tutorial.

MIN in Array in Google Sheets for Expanded Results in Each Row – QUERY

You can use the below formula to automate finding the minimum values in every row in your sheet. It is as per my above sample data range A3:G.

Formula 1 (Master Formula):

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

Can I use this Google Sheets MIN array formula to find the minimum value in each row in my sheet?

Yes, you can! You only need to change the range reference as per the image below.

In my master formula above, it’s A3:G. Now, I have changed it to A2:C as per the new data range below.

Formula Insight

I’m sure you are keen to know how I’ve formed this MIN Array formula in Google Sheets. Here it’s.

The Query function in Google Sheets can find minimum values in columns.

We can use this feature to find minimum values in rows by changing the data orientation from row to column.

To do this, we can use the Google Sheets Transpose function.

The Query formula to find the minimum values in each row in expandable form is explained below.

Formula 2 (Alternative to Formula 1):

We can use the below Query for the current data in four rows (please refer to the first screenshot above).

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

Here in this formula, I’ve transposed the rows to columns and used them as the columns in the Query Select clause as it (the function) can return the minimum value in each column.

Here, column Col1, Col2, Col3, and Col4 represents row 1, row 2, row3, and row 4. So this formula is limited to 4 rows.

It is the logic that I have used in my Formula 1 above (master formula) to find MIN in Array in Google Sheets.

But as I’ve mentioned above, it (Formula 2) is limited to the existing number of rows in the range.

As you can see, the "Select Min(Col1), Min(Col2), Min(Col3), Min(Col4)" in Formula 2 is not the same as Formula 1. There I’ve used a combination of functions to overcome the row limitation.

In Formula 1, I’ve used the functions Join, Len, Regexreplace, Row, and the ampersand sign to automate the number of rows. It can automatically generate Col1, Col2, etc., depending on the number of rows in my data.

How Can I Automate Column Numbering as Above in Query?

I know this question is in your mind. Here is a similar tutorial where you can get the answer to this question – How to Find Max Value in Each Row in Google Sheets

There I’ve given more details. I mean, how to use the above Join, Len, Row, and Regexreplace to automate the number of columns in Query. Please check that.

Important:

The JOIN is one of the functions used in my formula above. It has a proven/known limitation as it won’t work in a large dataset. If you face any such issue, I have an alternative formula using DMIN here – Row-Wise MIN Using DMIN in Google Sheets.

MIN in Array in Google Sheets for Expanded Results in Each Row – BYROW

In the QUERY above, we have used the range A3:G where A3:A contains texts.

In the below BYROW formula, we require to use the numeric range, i.e., B3:G.

Formula:

=byrow(B3:G,lambda(r,if(counta(r)=0,,min(r))))

The r in MIN and COUNTA represents a single row in the range B3:G, and in the first row, it will be B3:G3

But in each row, it changes. It will become B4:G4, B5:G5, B6:G6, and so on.

The role of COUNTA is to return blank if a row has no value.

That’s all. I hope you could understand how to use MIN in Array in Google Sheets for Expanded Results. Enjoy!