Analistable

Reusable lookups with LET and LAMBDA

By the Analistable team · Updated · 2 min read

LET names the parts of a formula so a long lookup is calculated once and readable. LAMBDA goes further: save a formula in Formulas → Name Manager as, say, CUSTOMERNAME = LAMBDA(id, XLOOKUP(id, Customers[Customer ID], Customers[Name], "Not found")), and use =CUSTOMERNAME(A2) anywhere in the workbook. Both need Microsoft 365 or Excel 2021 (LET) / Excel 2024 (LAMBDA).

Part of our guide: How to join spreadsheets on a common column

LET: name the pieces

Without LET, a lookup that cleans the key and handles missing values repeats itself:

=IF(ISNA(XMATCH(TRIM(A2), Customers[Customer ID])), "Not found", XLOOKUP(TRIM(A2), Customers[Customer ID], Customers[Name]))

With LET, each part has a name and is calculated once:

=LET(
  id, TRIM(A2),
  name, XLOOKUP(id, Customers[Customer ID], Customers[Name], "Not found"),
  name
)

LAMBDA: your own function

  1. Choose Formulas → Name Manager → New.
  2. Name: CUSTOMERNAME.
  3. Refers to: =LAMBDA(id, XLOOKUP(TRIM(id), Customers[Customer ID], Customers[Name], "Not found")).
  4. Click OK. Now =CUSTOMERNAME(A2) works in any cell of the workbook.

Test a LAMBDA in a cell first by calling it directly: =LAMBDA(id, ...)(A2).

A reusable two-key lookup

Matching on two columns (for example Store and SKU) is a common join need. As a LAMBDA:

=LAMBDA(store, sku, XLOOKUP(1, (Stock[Store]=store) * (Stock[SKU]=sku), Stock[Qty], 0))

Saved as STOCKQTY, it's used as =STOCKQTY(B2, C2).

STOCKQTY(store, sku) results
StoreSKU=STOCKQTY(A2,B2)
LeedsAB-10014
LeedsAB-1010
YorkAB-1006

Apply to a whole column with MAP

=MAP(Orders[Customer ID], LAMBDA(id, CUSTOMERNAME(id))) returns a name for every order in one spilled formula.

LAMBDA functions live in the workbook. Colleagues need Microsoft 365 or Excel 2024 to use them; older versions show #NAME?. For the basics of matching tables, see joining spreadsheets.

A reusable “lookup or flag” function

Often you want the value if it exists and a clear flag if not, so missing keys can be fixed at the source:

LOOKUPORFLAG = LAMBDA(key, keys, values,
  LET(k, TRIM(key),
      r, XLOOKUP(k, keys, values, "#MISSING"),
      IF(k = "", "", r)))

Use it as =LOOKUPORFLAG(A2, Customers[Customer ID], Customers[Country]). Blank keys return blank, unknown keys return #MISSING, which you can filter for.

Test before you save

  • Call the LAMBDA inline first: =LAMBDA(x, x*2)(5) returns 10.
  • Keep parameter names short but clear; they appear in the formula tooltip.
  • Add a comment in the Name Manager's Comment box describing the arguments — it shows when typing the function.
  • If the result is #CALC!, you probably entered the LAMBDA in a cell without calling it.

Sharing LAMBDA functions

A LAMBDA saved in the Name Manager travels with the workbook. To reuse it elsewhere, copy a sheet that uses it into the other workbook — the name comes along — or keep a template workbook with your standard functions.

Frequently asked questions

What is the difference between LET and LAMBDA?
LET names values inside one formula. LAMBDA creates a function with parameters that you can save in the Name Manager and reuse.
Which Excel versions support LAMBDA?
Microsoft 365 and Excel 2024. LET is also in Excel 2021.
Can I use a LAMBDA in another workbook?
Not automatically. Copy a sheet that uses it into the other workbook, or recreate the name there.

Related guides