Excel - LAMBDA functions

Excel's new LAMBDA functionality (Office 365/Excel 2021, beta version, insiders only) - the capability to define your own function within Excel directly - makes that several User Defined Function (UDF) written in VBA can be converted into a faster executing LAMBDA function equivalent.

Download demo file   (205kB - downloaded 32 times - Latest version: 2021-10-17-v23 - Same file as UDF demo file)

If you do not have LAMBDA functionality yet, then you can still use UDF or regular Excel formula alternatives.

With a UDF you can do more than with a LAMBDA function. Reason: LAMBDA functions can only pick up cell values, whereas UDF can also take into account other properties (e.g. color, height, width, visibility) to determine how to manipulate input into output. So, not all UDF can be converted into LAMBDA function. Though UDF can take into account other properties its output is still values only, like any function. Manipulating other properties can only be done via a VBA sub procedure a.k.a. macro.


To define a LAMBDA function: go to menu Formulas, go to Name Manager, press button New... enter Name, and enter the LAMBDA formula under Refers To.
You can simply copy the function codes from the text areas on this page into the Refers To section.

Developing a LAMBDA function can be done directly in a worksheet, and you can test if it works, before you enter it under the Name Manager.
Please see Microsoft support page about LAMBDA function for a full explanation.



Dependent on your Excel setting you need to use semicolon or colon in formulas. Choose your setting.

Comma
Semicolon


LAMBDA - Concatenate (L_ConcatenateX)

Concatenates all cells in a range. It has arguments Inputrange, Separator, optional By_columns (if False/empty then it concatenates row by row, else it concatenates column by column), optional Iterator (used for recursion - just leave it empty).


UDF ConcatenateX is more robust
LAMBDA function L_ConcatenateX is an iterative function, that calls itself. Currently, LAMBDA functions can only do a limited amount of iterations, so it can only process a limited input range (1000-2000 cells), which may be enough in your case. The UDF does not have such a limitation.

LAMBDA - Get nth word from a string (L_GetWord)

Gets the nth word from an inputstring. It has arguments Inputstring, Separator, n, optional Backwards (if TRUE, then word counting starts from the end of string, else from the start).


LAMBDA - Gets all or n words from a string, starting at word m (L_GetWords)

Gets all or n words from an inputstring, starting at word m. It has arguments Inputstring, Separator, optional StartFrom (= m), optional NumWords (= n), optional Backwards (if TRUE, then the counting of m starts from the end of the string).
If the string contains less than n words counting from the mth word, then the function will only return all words available from m till the end.


Find in cells (L_FindInCells)

Finds a value with cells. It has as arguments Searchvalue, Searchrange, MatchLength (if only exact match qua length are accepted). It is a basic version compared to the more evolved UDF.
Based on L_RangeToList, so you need to copy in that function as well.

LAMBDA - Range to list (L_RangeToList)

Converts a 2-dimensional range into a 1-dimensional list. It has argument Inputrange, and optional By_columns (if true then it works column by column, otherwise row by row which is the default).


LAMBDA - Reverse range (L_ReverseRange)

Swaps rows, columns, or both rows and columns of a range. It has arguments Inputrange, optional ReverseRows (true/false), optional ReverseColumns (true/false).

LAMBDA - As-The-Crow-Flies-Distance (L_GlobeDistance)

Calculates the as-the-crow-flies-distance, the shortest distance between two geographical locations specified by latitude and longitude. It has arguments FromLatitude, FromLongitude, ToLatitude, ToLongitude, InMiles (if TRUE, then distance in miles is returned, else in kilometers).
Seee also separate webpage as-the-crow-flies distance function Demo file contains three alternatives: regular Excel formula, UDF (VBA), and LAMBDA function.



LAMBDA - Linear interpolation (L_InterpolateX)

Interpolates within a table, on one dimension X or Y. It has arguments SearchValue, SearchRange, ValueRange.
See also separate page Excel - Linear Interpolation for a full explanation.


LAMBDA - Bilinear interpolation (L_InterpolateXY)

Interpolates within a table, on two dimensions X and Y. It has arguments searchX, searchY, rangeX, valuerange (top row only!).
It uses L_InterpolateX so copy that function in as well.
See also separate page Excel - Bilinear Interpolation for a full explanation.