(C) 2009 - 2021 by Mourad Louha · Alle Rechte vorbehalten

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.

Custom Array Functions

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.

LAMBDA

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.

Custom Array Functions in the Name Manager

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.

ARRAY.APPEND

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.

ARRAY.CREATE

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.

ARRAY.FROMTEXT

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.

ARRAY.FROMTEXT.EX

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.

ARRAY.PUSH

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)

ARRAY.PULL

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)

ARRAY.REMOVE.FIRSTN

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)

ARRAY.REMOVE.LASTN

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)

ARRAY.REVERSE

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.

ARRAY.SPLIT

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:

ARRAY.SPLIT Matrix

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.

ARRAY.SPLIT.BYPOS

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.

ARRAY.SPLIT.BYPOS Positions

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 :-)

 

 
Comments

Thank you for sharing and taking the time to post in English :)

Hi Ralph – thank you :-)

Best,
Mourad

Thanks for sharing these! Question, without having fully reviewed how these are used throughout your project, why not this:

Y, SEQUENCE(ROWS(Array)+1,1,0),

This preserves the existing array and pushes the new entry on. I’m sure it makes sense, but just curious. Thanks again!

Apologies, was sure I mentioned it, but apparently not: I’m referring to ARRAY.PUSH.

Hi TB,

thank you very much. If I modify the formula as you suggested, a new entry is added on top of the array. That’s not the purpose of my function, which should move all items one down, but preserve the size. So, e.g. {A,B,C} becomes with my function {X,A,B} and with your suggestion {X,A,B,C}.

I assumed (and hope), that I correctly added your suggestion my function, so that it becomes:

=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,1,0),
        IF(Y<1,IFERROR(INDEX(D,1,X),""),INDEX(Array,Y,X))))

However, I like your suggestion in any case, as that would be a possibility to extend the array by one item on top and therefore be surely useful as a new LAMBDA.

Best,
Mourad

Hello Mourad,
Thanks for your work!
I looking for a lambda function which transform a matrix into lines.
For exemple
Col_A Col_B Col_C Col_D
L1 Line1 AA BB
L2 Line2 CC
Result in this case : 2 colums list

Col_A Col B
Line1 AA
Line1 BB
Line2 CC
Could you help me on this?
Thanks in advance
Regards
Didier

Hello Mourad!
Another request. I would a treeview lambda fucntion (recursive)
Exemple :
ColA ColB ColC
ID Parent ID Name
01 Top
02 01 Child1
03 01 Child2
04 03 Sub-child2-1
Expected result
ColA ColB ColC
Line1 Top
Line2 Child1
Line3 Child2
Line4 Sub-Child2_1

Thanks in advance,
Didier

Mourad Louha

Hi Diddou,

thank you!

Currently, I am too much involved in a project, so my time is limited. But I suggest the following: let’s talk via e-mail in the next days. Therefore, can I write you an e-mail to the e-mail address you leaved here? Or can you just send me an e-mail to vba(at)maninweb(dot)de.

Best,
Mourad

Hello Mourad and thanks for your post! I have worked from it to develop a Lambda that I need for a work project, but I have failed so far… Perhaps you have an idea how to create a function for to repeat a range n number of times? As in an ‚ARRAY.REPEAT‘ function.

The range to be repeated is dynamic (as in $F$5#), and the number of times to repeat is a function like ‚=counta(filter(table1[column1];table1[column2]=999))‘.

Any and all help would be greatly appreciated!

Thanks and best regards,

Ronnie

Hello again Mourad, I walked away and let the subconscious mind work for a bit and came up with an answer myself. It is based in a Index / Mod / Sequence combination and in my specific case the formula looked like this:

=INDEX(‚Applied formulas‘!$E$5#;MOD(SEQUENCE(ROWS(‚Applied formulas‘!$E$5#)*COUNTA(UNIQUE(FILTER(TransData[Entity];TransData[Service]=999)));1;0;1);ROWS(‚Applied formulas‘!$E$5#))+1)

Best regards,

Ronnie

Hi Ronnie,

I am very sorry, I did not view your comment sooner. There are some private reasons, I could not check my blogs regularly the last weeks. However, I am happy, that you found a solution.

Best,
Mourad