BYCOL function

This function groups an array by columns by application of a LAMBDA function to each column.

Sample Usage

BYCOL(A1:C3,LAMBDA(column,MAX(column)))

BYCOL(A1:C3,LAMBDA(column,SUM(column)))

Syntax

BYCOL(array_or_range,LAMBDA)

  • array_or_range: An array or range to be grouped by columns.
  • LAMBDA: A LAMBDA that’s applied to each column in the given array or range to obtain its grouped value.
    • Syntax: LAMBDA(name,formula_expression)
    • Requirements:
      • The LAMBDA must have exactly 1 name argument along with a formula_expression which uses that name. The name resolves to the current column being grouped when the LAMBDA is applied.

Notes

  • The passed LAMBDA should accept exactly 1 name argument, otherwise an #N/A error is returned. This argument corresponds to a column in the input array.
  • Every column should be grouped to a single value. Array results for grouped values aren’t supported.
  • A named function can be passed for the LAMBDA parameter and behaves like a LAMBDA in this case. Learn more about named functions.
    • There should be exactly 1 argument placeholder defined for it.
    • Parenthesis shouldn't follow the named function.

Examples

Returns a 1x3 row-array with max of each column

Example data:

 

A

B

C

1

3

5

7

2

4

3

5

3

1

2

4

Example: =BYCOL(A1:C3,LAMBDA(column,MAX(column)))

Result:

4

5

7

Returns the name of the sales representative with average sales greater than or equal to 30

Example data:

 

A

B

C

D

1

 

Ally

Brian

Lily

2

2019

20

10

20

3

2020

50

15

30

4

2021

30

30

15

Example: =FILTER(B1:D1,BYCOL(B2:D4,LAMBDA(col,AVERAGE(col)>=30)))

Result:

Ally

Returns a 1x3 array with the difference between max and min value of each column using a Named function as LAMBDA

Make a Copy

 

Example data:

 

A

B

C

1

3

5

7

2

4

3

5

3

1

2

4

Example: =BYCOL(A1:C3,MAX_MIN_DIFF)

Named function: MAX_MIN_DIFF is a named function which outputs the difference between the maximum value and the minimum value.

Formula definition: =MAX(col)-MIN(col), where col is an argument placeholder defined for MAX_MIN_DIFF.

Result:

3

3

3

Common Errors

The passed LAMBDA doesn’t have exactly 1 name argument

If the passed LAMBDA doesn’t have exactly 1 name argument, and 1 formula expression as an argument, this error occurs:

“Wrong number of arguments to LAMBDA. Expected 1 argument, but got 2 arguments.”

Example: =BYCOL(C1:C4,LAMBDA(a,b,a+b))

In this example, LAMBDA was given 2 name arguments when it only needs 1.

The last parameter of BYCOL wasn’t a LAMBDA

If the last parameter of BYCOL wasn’t a LAMBDA, this error occurs:

“Argument must be a LAMBDA.”

Example: =BYCOL(C1:C4,4)

The LAMBDA passed to BYCOL was incorrect

If 1 or more name arguments weren’t valid, this error occurs:

“Argument 1 of function LAMBDA is not a valid name.”

Example: =BYCOL(C1:C4,LAMBDA(C1,C1+1))

In this example, C1 is an invalid name since it clashes with a range.

The application of LAMBDA on the input array doesn’t group each column to a single value

If the application of LAMBDA on the input array doesn’t group each column to a single value, this error occurs:

“Single value expected. Nested array results are not supported.”

Example: =BYCOL(C1:C3,LAMBDA(col,col))

Related functions

  • LAMBDA function: This function lets you create and return a custom function with a set of names and a formula_expression that uses them.
  • MAP function: This function maps each value in the given arrays to a new value.
  • REDUCE function: This function reduces an array to an accumulated result.
  • BYROW function: This function groups an array by rows.
  • SCAN function: This function scans an array and produces intermediate values.
  • MAKEARRAY function: This function creates a calculated array of specified dimensions.
  • Create & use named functions: This function lets you create and store custom functions, similar to LAMBDA.
true
Visit the Learning Center

Using Google products, like Google Docs, at work or school? Try powerful tips, tutorials, and templates. Learn to work on Office files without installing Office, create dynamic project plans and team calendars, auto-organize your inbox, and more.

Search
Clear search
Close search
Google apps
Main menu
6457458610647879942