Disclaimer: use at your own risk
© Stelling Consulting, 2008-2024  

Excel Various LAMBDA functions

Excel's LAMBDA functionality (since Office 365/Excel 2021) - the capability to define your own function within Excel's Name Manager directly (without VBA code) - makes that several User Defined Functions (UDF) written in VBA can be converted into a faster executing LAMBDA function equivalents.

Download demo file   (205kB - downloaded 1138 times - Latest version: 2022-01-06 - Same file as UDF demo file)

If you do not have Office 365/Excel 2021, then you can still use VBA based UDF or regular Excel formula alternatives from this file.

Technical note
With a VBA based UDF you can do more than with a LAMBDA function. Reason: LAMBDA functions can only pick up cell values, whereas VBA based 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 VBA based UDF can be converted into LAMBDA function. Though a VBA based 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.



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


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.


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.

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


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

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 (optional, if TRUE, then distance in miles is returned, if omitted/FALSE, then in kilometers = default.)
Seee also separate webpage as-the-crow-flies distance function. The demo file contains three alternatives: regular Excel formula, UDF (VBA), and LAMBDA function.



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.


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.