This reference lists 23 Excel lambda functions, ordered by frequency of use, with high-frequency functions marked ⭐. Each function includes its syntax and an example.
| Function | Description | Example |
|---|---|---|
LAMBDA ⭐ |
Create custom, reusable functions and call them by a friendly name | =LAMBDA(x,x*2)(5) — Creates a reusable Lambda function that doubles a number |
VSTACK ⭐ |
Appends arrays vertically and in sequence to return a larger array | =VSTACK(A1:C3,D1:F3) — Stacks A1:C3 and D1:F3 vertically |
HSTACK ⭐ |
Appends arrays horizontally and in sequence to return a larger array | =HSTACK(A1:C3,D1:F3) — Stacks A1:C3 and D1:F3 horizontally |
LET ⭐ |
Assigns names to calculation results to allow storing intermediate calculations, values, or defining names inside a formula | =LET(x,A1,y,B1,x+y) — Defines variables x and y, then calculates their sum |
MAP |
Returns an array formed by mapping each value in the array(s) to a new value by applying a LAMBDA to create a new value | =MAP(A1:A5,LAMBDA(x,x*2)) — Doubles each value in A1:A5 |
REDUCE |
Reduces an array to an accumulated value by applying a LAMBDA to each value and returning the total value in the accumulator | =REDUCE(0,A1:A5,LAMBDA(x,y,x+y)) — Accumulates all values in A1:A5 into a total |
SCAN |
Scans an array by applying a LAMBDA to each value and returns an array that has each intermediate value | =SCAN(0,A1:A5,LAMBDA(x,y,x+y)) — Returns each intermediate step of the running total |
MAKEARRAY |
Returns a calculated array of a specified row and column size, by applying a LAMBDA | =MAKEARRAY(3,3,LAMBDA(r,c,r*c)) — Creates a 3x3 multiplication table array |
BYROW |
Applies a LAMBDA to each row and returns an array of the results | =BYROW(A1:C3,LAMBDA(row,SUM(row))) — Sums each row of A1:C3 |
BYCOL |
Applies a LAMBDA to each column and returns an array of the results | =BYCOL(A1:C3,LAMBDA(col,SUM(col))) — Sums each column of A1:C3 |
ISOMITTED |
Checks whether the value in a LAMBDA is missing and returns TRUE or FALSE | =LAMBDA(x,y,IF(ISOMITTED(y),x,x+y))(5) — Checks whether an optional LAMBDA parameter was omitted |
SINGLE |
Returns a single value using implicit intersection | =SINGLE(A1:A5) — Returns a single value from an array |
ARRAYTOTEXT |
Returns an array of text values from any specified range | =ARRAYTOTEXT(A1:C3) — Converts an array into a text string |
VALUETOTEXT |
Returns text from any specified value | =VALUETOTEXT(123) — Converts a number to text |
DROP |
Excludes a specified number of rows or columns from the start or end of an array | =DROP(A1:A10,2) — Drops the first 2 rows from A1:A10 |
TAKE |
Returns a specified number of contiguous rows or columns from the start or end of an array | =TAKE(A1:A10,5) — Takes the first 5 rows from A1:A10 |
CHOOSEROWS |
Returns the specified rows from an array | =CHOOSEROWS(A1:C10,1,3,5) — Returns rows 1, 3 and 5 of A1:C10 |
CHOOSECOLS |
Returns the specified columns from an array | =CHOOSECOLS(A1:C10,1,3) — Returns columns 1 and 3 of A1:C10 |
TOROW |
Returns the array in a single row | =TOROW(A1:C3) — Converts A1:C3 into a single row |
TOCOL |
Returns the array in a single column | =TOCOL(A1:C3) — Converts A1:C3 into a single column |
WRAPROWS |
Wraps the provided row or column of values by rows after a specified number of elements | =WRAPROWS(A1:J5,3) — Wraps A1:J5 into rows of 3 elements |
WRAPCOLS |
Wraps the provided row or column of values by columns after a specified number of elements | =WRAPCOLS(A1:J5,3) — Wraps A1:J5 into columns of 3 elements |
EXPAND |
Expands or pads an array to specified row and column dimensions | =EXPAND(A1:B2,4,4,"") — Expands A1:B2 into a 4x4 array |
From Online Toolbox (www.vba.net)