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
- Choose Formulas → Name Manager → New.
- Name:
CUSTOMERNAME. - Refers to:
=LAMBDA(id, XLOOKUP(TRIM(id), Customers[Customer ID], Customers[Name], "Not found")). - 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).
| Store | SKU | =STOCKQTY(A2,B2) |
|---|---|---|
| Leeds | AB-100 | 14 |
| Leeds | AB-101 | 0 |
| York | AB-100 | 6 |
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
- Write the formula in a cell with real references first, for example
=XLOOKUP(TRIM(A2), Customers[Customer ID], Customers[Name], "Not found"). - Replace each input that changes with a parameter name: A2 becomes
id. - Wrap it:
=LAMBDA(id, XLOOKUP(TRIM(id), ...))(A2)and confirm it returns the same value as step 1. - Copy everything except the trailing
(A2), then paste it into Refers to in a new Name Manager entry. - Test the saved name on a few known keys, including one that doesn't exist and one blank cell.
Troubleshooting
| Symptom | Cause | Fix |
|---|---|---|
| #NAME? | Function not supported, or the name is misspelt | Check the Excel version and the Name Manager entry |
| #CALC! | LAMBDA entered in a cell without being called | Add the arguments in brackets after it, or save it as a name |
| #VALUE! | Called with the wrong number of arguments | Pass one value for each parameter, in order |
| Wrong values after sorting the data | Ranges inside the LAMBDA are fixed cell addresses | Use table references such as Customers[Name] |
| LET name rejected | Name 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.