Analistable

Reusable lookups with LET and LAMBDA

By the Analistable team · Updated · 5 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.

Google Sheets also supports LAMBDA, plus Data → Named functions, which plays the role of the Name Manager: give the function a name, list its argument placeholders and paste the formula body. Named functions can be imported into other spreadsheets from the same menu.

A reusable inner-join function

LET and LAMBDA together can wrap the FILTER/XMATCH pattern from inner join in Excel into a function that returns the matching rows of one table plus one column from another:

JOINCOL = LAMBDA(left, leftKey, rightKey, rightCol,
  LET(hit, ISNUMBER(XMATCH(leftKey, rightKey)),
      HSTACK(FILTER(left, hit),
             XLOOKUP(FILTER(leftKey, hit), rightKey, rightCol))))

=JOINCOL(Orders, Orders[Customer ID], Customers[Customer ID], Customers[Name]) spills every order that has a known customer, with the name added as the last column. HSTACK needs Microsoft 365 or Excel 2024.

Building a LAMBDA step by step

  1. Write the formula in a cell with real references first, for example =XLOOKUP(TRIM(A2), Customers[Customer ID], Customers[Name], "Not found").
  2. Replace each input that changes with a parameter name: A2 becomes id.
  3. Wrap it: =LAMBDA(id, XLOOKUP(TRIM(id), ...))(A2) and confirm it returns the same value as step 1.
  4. Copy everything except the trailing (A2), then paste it into Refers to in a new Name Manager entry.
  5. Test the saved name on a few known keys, including one that doesn't exist and one blank cell.

Troubleshooting

LET and LAMBDA errors
SymptomCauseFix
#NAME?Function not supported, or the name is misspeltCheck the Excel version and the Name Manager entry
#CALC!LAMBDA entered in a cell without being calledAdd the arguments in brackets after it, or save it as a name
#VALUE!Called with the wrong number of argumentsPass one value for each parameter, in order
Wrong values after sorting the dataRanges inside the LAMBDA are fixed cell addressesUse table references such as Customers[Name]
LET name rejectedName looks like a cell reference (for example `ab1`)Pick a name that isn't a valid cell address

When to use LET, LAMBDA, or neither

  • LET whenever a formula repeats the same sub-expression or is hard to read. It changes nothing for colleagues on Excel 2021 or later.
  • LAMBDA when the same lookup logic appears on several sheets and you want one place to fix it.
  • LAMBDA with MAP, BYROW or REDUCE when you need a per-row calculation that an ordinary formula can't spill, such as a lookup that applies a different rule depending on another column.
  • Neither when the file goes to people on Excel 2019 or earlier. Plain INDEX/MATCH is safer there; see VLOOKUP vs XLOOKUP vs INDEX MATCH.
  • Power Query instead when the lookup is really a join between two tables that is refreshed every month; see joining two tables in Excel.

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.
Is a LAMBDA slower than writing the formula out?
Not noticeably for ordinary lookups: the saved function runs the same calculation. Speed depends on what's inside, such as how many rows XLOOKUP or FILTER has to scan.
Can a LAMBDA have optional arguments?
Yes. Put the parameter name in square brackets when defining it and test for it with ISOMITTED, which lets you supply a default value.
Does Google Sheets have LAMBDA?
Yes. Google Sheets supports LET and LAMBDA, and Named functions under the Data menu let you save a formula with arguments and reuse it.

Related guides