Using LAMBDA functions

This page has moved. The current version of this page is at https://epplussoftware.com/docs/formulas-and-filters/formula-calculation/using-lambda-functions/. The EPPlus documentation is no longer updated in this wiki; please use the EPPlus guides and update your bookmarks.

EPPlus supports LAMBDA functions from version 8.2. LAMBDA lets you define custom, reusable formulas directly in the workbook — without needing VBA or external code. The EPPlus calculation engine supports the full range of LAMBDA capabilities, including defined names, recursion, currying and eta reduction.

A LAMBDA function can be stored as a defined name, making it reusable across the entire workbook just like a built-in function.

using var package = new ExcelPackage();
var sheet = package.Workbook.Worksheets.Add("Sheet1");

// Define a reusable division function
package.Workbook.Names.AddFormula("Divide", "LAMBDA(x, y, x/y)");

sheet.Cells["A1"].Formula = "Divide(12, 3)";
sheet.Calculate();
var result = sheet.Cells["A1"].Value; // result is 4

A LAMBDA can call itself, which allows you to implement iterative algorithms directly in a formula. The example below defines a recursive factorial function as a defined name.

// Factorial(n) = n * Factorial(n-1), base case at n = 0
// Note: the sheet name prefix is required when a defined name calls itself recursively
ws.Names.AddFormula("Factorial",
    "LAMBDA(input, LET(n, input, IF(n = 0, 1, n * Sheet1!Factorial(n - 1))))");

ws.Cells["A1"].Formula = "Factorial(4)";
ws.Calculate();
// Result: 24

A LAMBDA can return another LAMBDA, allowing you to build up a calculation by supplying one argument at a time. This technique is known as currying.

// Each call supplies one argument and returns a new function
// until all three arguments are provided
sheet.Cells["A1"].Formula = "LAMBDA(a, LAMBDA(b, LAMBDA(c, a + b + c)))(1)(2)(3)";
sheet.Calculate();
// Result: 6

In higher-order functions like MAP, REDUCE or SCAN, you normally wrap a function in a full LAMBDA to pass it as an argument. Eta reduction is a shorthand from lambda calculus that lets you pass the function directly instead, omitting the wrapper entirely.

Excel represents this using the _xleta prefix — this is part of the OOXML specification, not an EPPlus-specific construct. EPPlus supports this syntax from version 8.2.

// Standard approach — wrap UPPER in a full LAMBDA
sheet.Cells["B1"].Formula = "MAP(A1:A3, LAMBDA(x, UPPER(x)))";

// Eta reduced — pass UPPER directly using the _xleta prefix
sheet.Cells["B2"].Formula = "MAP(A1:A3, _xleta.UPPER)";

Both formulas produce the same result. The eta reduced form is more concise and is the notation Excel itself uses internally when saving certain formulas to the file format.

• Formula calculation