Summary of my custom LAMBDAs for arrays

Microsoft released early December 2020 the first version of the LAMBDA function in Excel. This new function allows you to define your own re-usable custom functions by only using the Excel’s formula language. In the past, custom functions could only be implemented by using a programming language such as VBA or JavaScript. At the time, I wrote this article (12/22/2020), the LAMBDA function is only available for Office Insiders. Anyone can join the Office Insider Program, please see here for more information on that.

The new function LAMBDA will be in my opinion a game changer and I think that the Excel team is not exaggerating, if they are talking about that LAMBDA will revolutionize how you build formulas in Excel.

I started blogging about the LAMBDA function since its release and created eleven custom array related functions until now. I am mainly writing my posts in German, but the last days I noticed a considerable increase of English-speaking readers. Online translators are nowadays doing a pretty good job when it comes to translate text, but they mostly fail when it comes to translate Excel formulas. That makes it more difficult for English-speaking people to understand the German formulas. Therefore, I am writing this article in English with a summary and short explanation of all my custom functions created so far.

As you probably know, nothing is static in Excel formulas. Often, there may be a way to improve it. So, if you find a better solution for one or more formulas, please let me know and leave a comment here.

LAMBDA

The syntax of the LAMBDA function is relatively simple: define the input parameters you need for your function as arguments within the LAMBDA function. It is recommended to give those parameters a meaningful name and avoid cell addresses as parameter names, as Excel will not allow that.

The last argument within the LAMBDA function should be the calculation. That can be a direct calculation, like shown in the picture above or the result of a LET formula – which then also can use the input parameters and additionally define variables for intermediate calculations. For testing a LAMBDA function, you can call it within a cell by passing the input values after the definition, for example like this: LAMBDA(Y,Y,X+Y)(1,2).

Once your LAMBDA function is ready and tested and you want to make a custom function from it, add a new name in the Name Manager and set the reference to your LAMBDA function without passed arguments. After then, you can use the name as your custom function. The following picture shows my custom functions created for this article.

If you are new to LAMBDAS, please also have a look on the great videos from MrExcel here and ExcelIsFun here. The announcement from the Excel team can be found here.

ARRAY.APPEND

The function ARRAY.APPEND was one of the first functions I developed as a custom LAMDBA function. Some weeks ago, my Excel colleague Andreas Thehos and I developed a LET formula for appending one range to another. I have written a German article Zwei Bereiche per Formel zusammenführen about that here. My custom LAMBDA function extends the past developed formula by providing the possibility to append two ranges having a different number of columns. The syntax of ARRAY.APPEND is:

=ARRAY.APPEND(Array1,Array2,Defaults)

Some examples are:

=ARRAY.APPEND(A2:C9,D2:E4,{„-„,0,0})
=ARRAY.APPEND(A2:C9;D2:E4;“-„)
=ARRAY.APPEND(A2:C9;D2:E4;TRANSPOSE(A1:A3))

The picture below shows the result for the first example from above. The first array has 3 columns, the second one 2 columns. Therefore, the values starting from row 10 and in column H of the resulting array are displaying the default value 0.

The LAMBA function for ARRAY.APPEND encapsulates a LET formula using 5 variables for intermediate calculations. The formula is:

=LAMBDA(Array1,Array2,Defaults,
 LET(R,ROWS(Array1)+ROWS(Array2),
     C,MAX(COLUMNS(Array1),COLUMNS(Array2)),
     D,IF(COLUMNS(Defaults)>1,
          INDEX(Defaults,1,0),
          LEFT(INDEX(Defaults,1,1),
               SEQUENCE(1,C,
               LEN(INDEX(Defaults,1,1)),0))),
     X,SEQUENCE(1,C),
     Y,SEQUENCE(R),
       IF(Y<ROWS(Array1)+1,
          IFERROR(INDEX(Array1,Y,X),
                  IFERROR(INDEX(D,1,X),"")),
          IFERROR(INDEX(Array2,Y-ROWS(Array1),X),
                  IFERROR(INDEX(D,1,X),"")))))

The variables R and C simply calculate the number of rows and columns of the resulting array. The variable D performs a validation of the defaults passed to the function. If Defaults is for example a range with more than one column, the first row is retrieved from Default. If Default has only one column or is a single value, the first top left item is retrieved (also works for single values) and duplicated into an array created by a SEQUENCE function.

The variables X and Y create a sequence of indexes from C and R, so that we can retrieve the elements of both passed arrays. This done in the last calculation, where we check if we are in the first or second array by using the IF condition.

ARRAY.CREATE

The purpose of the function ARRAY.CREATE is to be able to create a dynamic array by specifying a number of rows and columns and fill the array elements with a default value – which can also be passed as a horizontal range and then fills the columns with different default values. The syntax of the function is:

=ARRAY.CREATE(Rows,Columns,Defaults)

Some examples are:

=ARRAY.CREATE(5,3,"Text")
=ARRAY.CREATE(5,3,{"A","B","C")
=ARRAY.CREATE(B2,B3, TRANSPOSE(B4:B6))

The following picture shows the result for the third example from above. The function creates a dynamic array with 5 rows, 3 columns and fills the 3 columns with A, B and C.

The LAMBDA function encapsulates a LET function, which is used to perform some validations on the passed default value, create two number sequences X and Y and finally calculate the result.

=LAMBDA(Rows,Columns,Defaults,
 LET(D,IF(COLUMNS(Defaults)>1,
          INDEX(Defaults,1,0),
          LEFT(INDEX(Defaults,1,1),
               SEQUENCE(1,Columns,
               LEN(INDEX(Defaults,1,1)),0))),
     X,SEQUENCE(1,Columns),
     Y,SEQUENCE(Rows,1,1,0),
       IFERROR(INDEX(D,1,X*Y),"")))

The variable D checks if the passed range for the parameter Default has more than one column. If yes, then we take the first row of the passed range. If not, then the default value is either a vertical range or a single value. In this case, we take the top left element of the range – which could also be a single value – and duplicate that value to a horizontal array created by the SEQUENCE function.

The variables X and Y both create a sequence of values. Assuming that the number of requested rows is 5 and the number of columns is 3, like from the example above, X creates the horizontal sequence {1,2,3} and Y creates the vertical sequence {1;1;1;1;1}, as the fourth argument (the step) within the SEQUENCE function is set to zero.

Multiplying X and Y then gives us a dynamic array with 5 rows and 3 columns, having in each column respectively the values 1,2 and 3, which are representing our indexes from the default array. The last calculation is then to extract the corresponding values from Default.

ARRAY.FROMTEXT

The function ARRAY.FROMTEXT is intended to create a dynamic array from a text value, as shown in the picture below.

The formula is relatively simple. By using the MID function, each character is extracted from the text and by using SEQUENCE an array of these characters is created. The formula is:

=LAMBDA(Value,MID(Value,SEQUENCE(LEN(Value)),1))

I am using ARRAY.FROMTEXT as helper function. For example, this function will be used later in a custom text splitter function.

ARRAY.FROMTEXT.EX

The function ARRAY.FROMTEXT.EX represents an extended version of ARRAY.FROMTEXT. In this case, I wanted to be able to split the text into blocks with a predefined size. Additionally, the function should offer me possibilities to define a direction from which the blocks are extracted, a character to fill up the created text blocks and optionally add new rows to the dynamic array. The syntax of the function is:

=ARRAY.FROMTEXT.EX(Value,Blocksize,Direction,FillCharacter,AdditionalRows)

Two examples are:

=ARRAY.FROMTEXT.EX(A2,4,1,"x",0)
=ARRAY.FROMTEXT.EX(A2,4,2,"x",1)

The following picture respectively shows the results in B2 and C2 from the two examples above.

I used a LET function to perform some intermediate calculations and then encapsulated the LET function into the LAMBDA function. The resulting LAMBDA function is:

=LAMBDA(Value,Blocksize,Direction,FillCharacter,AdditionalRows,
 LET(R,ROUNDUP(LEN(Value)/Blocksize,0)+
       IF(ISNUMBER(AdditionalRows),AdditionalRows,0),
     F,REPT(IF(ISBLANK(FillCharacter),CHAR(1),
       LEFT(FillCharacter,1)),R*Blocksize-LEN(Value)),
     S,IF(ISNUMBER(Direction),IFERROR(
       CHOOSE(Direction,Value&F,F&Value),Value),Value&F),
       SUBSTITUTE(MID(S,SEQUENCE(R,1,1,Blocksize),Blocksize),CHAR(1),"")))

So, what happens here? First, the variable R is calculated to retrieve the needed number of rows. This is done by rounding up the division of the text length by the block size. If additional rows are needed, that number is added to R.

Then, the fill character is repeated until the text length reaches a multiple of the block size. The result is stored in F. For example, the text length is 10 characters in the examples above. By defining a block size of 4, the next multiple is 12 and therefore F equal to "xx". If AdditionaRows is set to 1, then we add 4 additional characters and we obtain "xxxxxx". In case, no fill character has been given, we use a default character, which is CHAR(1).

Once F is calculated, we add F to the left or the right of the original text. That happens in the calculation for the variable S. If the passed value for Direction is empty or not equal 1 or 2, we use a default handling.

Finally, a sequence is created having a step of Blocksize. And each text block is extracted. In case, no fill character was specified, we replace the default fill character by nothing.

Someone could ask at this point, why do I need this function? Well, I guess under normal circumstances, this function will rarely be used. But I am planning more custom LAMBDA functions and one of them will be able to add very large numbers in Excel by only using a formula. In that function, I needed ARRAY.FROMTEXT.EX.

ARRAY.PUSH

Like the previous function for splitting a text into blocks, I needed a function for moving all elements of a one column array or range by one position down for designing a function which will be able to add large numbers in Excel. ARRAY.PUSH is a function which extends that to a multicolumn range and also offers the possibility to define default values. The syntax of the function is:

=ARRAY.PUSH(Array,Defaults)

The picture below shows a sample, where the values of the range A2:C9 are moved one row down and the first row is set to Z, 99 and 999.

The LAMBDA function for ARRAY.PUSH is defined as follows:

=LAMBDA(Array,Defaults,
 LET(D,IF(COLUMNS(Defaults)>1,INDEX(Defaults,1,0),TRANSPOSE(Defaults)),
     X,SEQUENCE(1,COLUMNS(Array)),
     Y,SEQUENCE(ROWS(Array),1,0),
       IF(Y<1,IFERROR(INDEX(D,1,X),""),
              INDEX(Array,Y,X))))

The first variable D within the LET function checks how the default values are passed to the function. It is expected that the defaults are passed as a single column or a single row range. If the default values are passed as a column range, the range is transposed.

The variables X and Y create a sequence of index positions from the range. Y starts at zero, what will move all array items by one down. An IF clause checks Y for zero and if that is the case, we take the values from Default.

ARRAY.PULL

Once I created ARRAY.PUSH, I thought to also create the opposite ARRAY.PULL, which moves all array row by one up. The picture below shows that for the range A2:C9. The syntax of the function is:

=ARRAY.PULL(Array,Defaults)

The LAMBDA function is very similar to the LAMBDA function for ARRAY.PUSH:

=LAMBDA(Array,Defaults,
 LET(D,IF(COLUMNS(Defaults)>1,INDEX(Defaults,1,0),TRANSPOSE(Defaults)),
     X,SEQUENCE(1,COLUMNS(Array)),
     Y,SEQUENCE(ROWS(Array),1,2),
       IF(Y>ROWS(Array),IFERROR(INDEX(D,1,X),""),
                        INDEX(Array,Y,X))))

Instead of starting the sequence for Y by zero, I started from position 2. And the IF clause then checks Y for a value greater than the number of rows of the passed range.

Of course, it would also been having possible to only define one function and specify in an additional argument the direction from where to move the array: down or up. But personally, I prefer to have two separate functions for that.

ARRAY.REMOVE.FIRSTN

While playing with LAMDA functions, I had the idea to have a look at the Power Query M functions and search for functions, which could also be useful as custom functions in Excel. Power Query M includes the two functions List.RemoveFirstN and List.RemoveLastN which remove the first or last specified N elements from a list. I reproduced a similar functionality in Excel. ARRAY.REMOVE.FIRSTN removes N elements from a given range or array. The syntax of the function is:

=ARRAY.REMOVE.FIRSTN(Array,Count)

The LAMBDA function is defined as follows:

=LAMBDA(Array,Count,
 LET(R,ROWS(Array),
     C,COLUMNS(Array),
     N,IF(ISNUMBER(Count),IF(AND(Count>0,Count<R),Count,0),0),
     X,SEQUENCE(1,C),
     Y,SEQUENCE(R-N,1,N+1),
       INDEX(Array,Y,X)))

The variables R and C define the number of rows and columns from the range and ensure a better readability within the LET function. The variable N checks if the indicated count is in between the number or rows. X and Y create a sequence for the indexes of columns and rows to address in the last calculation. Y then start at the first-row number not to be removed.

ARRAY.REMOVE.LASTN

The function ARRAY.REMOVE.LASTN removes a specified number of rows from the tail of the indicated range. The syntax of the function is similar to ARRAY.REMOVE.FIRSTN:

=ARRAY.REMOVE.LASTN(Array,Count)

And the LAMBDA function is similar to ARRAY.REMOVE.FIRSTN too:

=LAMBDA(Array,Count,
 LET(R,ROWS(Array),
     C,COLUMNS(Array),
     N,IF(ISNUMBER(Count),IF(AND(Count>0,Count<R),Count,0),0),
     X,SEQUENCE(1,C),
     Y,SEQUENCE(R-N),
       INDEX(Array,Y,X)))

The difference is here that the sequence of row number indexes created stops at the last row not to be removed.

ARRAY.REVERSE

The function ARRAY.REVERSE is the first function I created when experimenting with LAMBDA functions. The function just reverses an array. Its syntax is:

=ARRAY.REVERSE(Array)

The LAMBDA function is relatively simple:

=LAMBDA(Array,
 INDEX(Array,SEQUENCE(ROWS(Array),1,ROWS(Array),-1),
             SEQUENCE(1,COLUMNS(Array))))

By using the INDEX function, we are able to extract each element of the array or range. One sequence is created to loop through the columns and another sequence is created to loop through the rows. For the rows, we just reverse the index by starting from the last row and using -1 as step.

ARRAY.SPLIT

There are several ways to split a one-column range into multiple columns. It is possible to use the Text to Columns feature, which can be found on the Data tab. Or we can use Power Query, create a query and split the text there. Or we can use VBA. I wanted to use a formula. The result is my custom LAMDA function ARRAY.SPLIT. Its syntax is:

=ARRAY.SPLIT(Array,Separator,Default)

The function expects a one-column array of range, a separator and a default value. Some samples of its use are:

=ARRAY.SPLIT(A2:A5,"-","*")
=ARRAY.SPLIT(A2:A5,"-","")
=ARRAY.SPLIT(A2:A5,,)

The picture below shows the result for the first sample. The first column in B contains the values from the left of the first occurrence of the separator character. The column C contains the values between the first and second occurrence of the separator. The longest text in the source range is located in cell A4 and includes 4 times the separator, while the other entries only include 2 times the separator. ARRAY.SPLIT then uses the default for fill up the values in E2:F4.

The LAMBDA function is more complex in this case. The formula is:

=LAMBDA(Array,Separator,Default,
 LET(D,IF(ISBLANK(Default),"",INDEX(Default,1,1)),
     S,IF(ISBLANK(Separator)," ",INDEX(Separator,1,1)),
     A,S&INDEX(Array,0,1),
     C,MAX(LEN(A)-LEN(SUBSTITUTE(A,S,""))),
     P,SEQUENCE(ROWS(A),C),
     M,INDEX(A,ROUNDUP(INDEX(P,0,0)/C,0),1),
     T,CHAR(1)&SEQUENCE(1,C)&CHAR(1),
     V,SUBSTITUTE(M,S,T,SEQUENCE(1,C))&S,
     X,IFERROR(LEN(INDEX(T,1,SEQUENCE(1,C)))+
       FIND(INDEX(T,1,SEQUENCE(1,C)),V),0),
     Y,IF(X>0,IFERROR(FIND(S,V,X)-1,0),0),
       IF(Y>0,MID(V,X,Y-X+1),D)))

Several variables are defined. The variable D checks if the argument Default is empty or not (what makes it optional). If Default is empty, an empty string is used instead. Same validation for the separator, but then we use a blank. The variable A prepends the separator to the array, so that separator is always the first character. And A also ensures that only the first column of the array is taken. The next variable C calculates the maximum number of resulting columns needed for holding the splitted texts.

The variable P just creates a sequence of numbers. In the pictured example from above, this will be 1 to 5 in the first row, 6 to 10 in the second row and so on. P is then a matrix having 5 rows and 5 columns. Now we can retrieve the values from the array data in A. The result is in variable M, which in fact just repeats the value in each column. Related to the example from above, this is what is shown, when M is filled:

The next step is to create a unique separator array. This is done in variable T, which is a sequence of numbers enclosed by a character. I have chosen CHAR(1) and assume, that no combination of CHAR(1)NumberCHAR(1) will occur in the text to split. The variable V then replaces each occurrence of the original separator by T at the corresponding position.

After that, we retrieve the start and end positions X and Y of each separator from T and also consider the length of each value from T. The last step is to retrieve each text part between X and Y from the texts in A.

ARRAY.SPLIT.BYPOS

Once I developed ARRAY.SPLIT, I had the idea to create a function which does not split an array by using a separator but uses an array of fixed positions. The syntax of ARRAY.SPLIT.BYPOS is:

=ARRAY.SPLIT.BYPOS(Array,Positions,Default)

Some samples of its use are:

=ARRAY.SPLIT.BYPOS(A2:A5,B1:D1,"-")
=ARRAY.SPLIT.BYPOS(A2:A5,B2:B4,"")
=ARRAY.SPLIT.BYPOS(A2:A5,{2,5,8},"*")
=ARRAY.SPLIT.BYPOS(A2:A5,{8,5,2},)

The picture below shows the result corresponding to the third example from the samples above. The text is split by the positions 2, 5 and 8 and the default value is an Asterix.

The LAMBDA function is a little similar to ARRAY.SPLIT:

=LAMBDA(Array,Positions,Default,
 LET(D,IF(ISBLANK(Default),"",INDEX(Default,1,1)),
     S,IF(COLUMNS(Positions)>1,TRANSPOSE(INDEX(Positions,1,0)),Positions),
     F,SORT(UNIQUE(FILTER(S,ISNUMBER(S)*(S>1)))),
     A,INDEX(Array,0,1),
     L,MAX(LEN(A))+1,
     C,ROWS(F)+1,
     P,SEQUENCE(ROWS(A),C),
     M,INDEX(A,ROUNDUP(INDEX(P,0,0)/C,0),1),
     X,SEQUENCE(1,C,0),
     Y,SEQUENCE(1,C,1),
     U,IF(X>0,INDEX(F,X,0),1),
     V,IF(Y>C-1,L,INDEX(F,Y,0)),
       IF(LEN(M)>U,MID(M,U,V-U),D)))

Like for ARRAY.SPLIT, the default value is checked through the variable D. The positions, which can be indicated as a vertical or horizontal range is validated in S. Then S passes a second validation, which filters out any non-number values from S, then retrieves its unique values and sorts them. The variable A retrieves the first column of the passed range.

L calculates the maximum length of the texts and add 1 to that. C calculates the number of columns needed in the resulting array. P and M are acting similarly to ARRAY.SPLIT. M then holds in its columns a copy of each value from A per row.

X and Y are creating two sequences of numbers, the first one starting from 0 and the second from 1. X and Y are used in U and V for retrieving the positions by using an offset. This is how M, U and V look like for the example used above.

The last calculation then retrieves the text for each item in M between U and V. If the length of the item is shorter than U, then the default value is used.

Conclusion

LAMBDA functions are very cool. The possibilities seem endless. As stated from the Excel team, the current release (22.12.20) is only the beginning. Let’s see what the Excel team will offer us in the future.

Happy Exceling :-)