If you use Excel and Google Sheets and leverage the BYROW function, there’s a key difference to understand.
In Google Sheets, the BYROW function can return a two-dimensional array output. However, in Excel, it cannot.
The purpose of the BYROW function in both Excel and Google Sheets remains the same: applying a user-defined lambda function to an array separated by rows. The lambda function calculates and returns a single value for a row, and BYROW repeats this calculation for each row in the specified array.
Excel adheres to this principle. However, in Google Sheets, the BYROW function has the added capability of returning a two-dimensional array.
Before we delve into the key difference between the BYROW function in Excel and Google Sheets, let’s explore their similarities in operation. We’ll then illustrate this difference with three examples below.
Understanding the Common Ground: BYROW Operation in Both Platforms
We have the following data in A1:C2 in both Excel and Google Sheets:
| 5 | 4 | 10 |
| 1 | 5 | 4 |
The following formula in cell D1 will return 19 (5+4+10) in cell D1 and 10 (1+5+4) in cell D2:
=BYROW(A1:C2, LAMBDA(r, SUM(r)))The above formula works both in Excel and Google Sheets.
Now let’s take the same data. How do we sort them in ascending order row by row and return the following result?
| 4 | 5 | 10 |
| 1 | 4 | 5 |
In Excel, the BYROW function can’t return a 2-dimensional array, so we can rule out that possibility. However, in Google Sheets, we can achieve this. With the following examples, you can understand the difference between BYROW in Excel and Google Sheets.
BYROW in Excel Vs BYROW in Google Sheets
The following formulas will work only in Google Sheets. That’s the added advantage of the BYROW function in Google Sheets.
BYROW with SORT
The following formula will sort each row and return a two-dimensional array:
=BYROW(A1:C2, LAMBDA(r, TRANSPOSE(SORT(TRANSPOSE(r)))))How does this formula work?
At the time of writing this tutorial, the SORT function cannot sort a row in Google Sheets (though it’s possible in Excel). So, we first transpose the row values, sort them, and then transpose them back.
I’ve already explained this in one of my earlier tutorials here: How to Sort Horizontally (Columns Left to Right) in Google Sheets.
Here is the lambda function to sort a single row:
=LAMBDA(r, TRANSPOSE(SORT(TRANSPOSE(r)))) (A1:C1)Use this function within BYROW after removing the function call, which is (A1:C1), and that is the above BYROW formula.
You can read more about this here: Row-Wise Sorting in a 2-D Array in Google Sheets.
BYROW with RANK
Here is another comparison where BYROW works in Google Sheets but not in Excel. In this case, as well, the formula returns a two-dimensional array.
Sample Data (A1:C2):
| 30 | 25 | 24 |
| 25 | 28 | 24 |
The RANK function returns the rank of a specified value in a dataset. To obtain the rank of the array, utilize ARRAYFORMULA with it:
For instance, the following formula will return the rank of values in A1:C1:
=ARRAYFORMULA(RANK(A1:C1, A1:C1))Output:
| 1 | 2 | 3 |
Now, let’s write a Lambda function for this calculation:
=LAMBDA(r, ARRAYFORMULA(RANK(r, r))) (A1:C1)Use this Lambda function within BYROW for the array A1:C2 as follows:
=BYROW(A1:C2, LAMBDA(r, ARRAYFORMULA(RANK(r, r))))Output:
| 1 | 2 | 3 |
| 2 | 1 | 3 |
BYROW with SEQUENCE
Here is another example to explain the difference between BYROW in Excel and Google Sheets.
Assume you have the numbers 5, 4, and 1 in cells A1:A3. The following SEQUENCE formula will return the sequence numbers 1 to 5 in a row.
=SEQUENCE(1, A1)Here is the equivalent Lambda function.
=LAMBDA(r, SEQUENCE(1, r))(A1)We can use the array A1:A3 in BYROW and the above lambda function to generate a sequence in each row as follows.
=BYROW(A1:A3, LAMBDA(r, SEQUENCE(1, r)))| 1 | 2 | 3 | 4 | 5 |
| 1 | 2 | 3 | 4 | |
| 1 |
This works in Google Sheets but not in Excel.
Trying the above formulas that return a two-dimensional array in Excel will result in the #CALC error.
