Written and reviewed by FormulaCraft Team. Each formula on this page is run through our verification engine before publishing.
Last reviewed:
How to use LAMBDA with MAP across a range in Excel and Google Sheets
Topic:LAMBDA (reusable formulas)Excel & Google Sheets
=MAP(A2:A6, LAMBDA(x, IF(x>50, "High", "Low")))Try it with your data
Edit the grid or formula, then run it through a real spreadsheet engine — no signup.
Sample data — click any cell to edit
Runs server-side · free · no signup
Step by step
- 1Place your source values in A2:A6 (e.g. scores: 45, 72, 38, 91, 55).
- 2In B2, enter =MAP(A2:A6, LAMBDA(x, IF(x>50, "High", "Low"))) — the formula spills results into B2:B6.
- 3The LAMBDA argument x receives each cell value from A2:A6 in turn; the IF body applies your logic.
- 4To use two parallel arrays, add a second range: =MAP(A2:A6, B2:B6, LAMBDA(x,y, x*y)) — both arrays must be the same size.
- 5Adjust the LAMBDA body for any per-cell transformation: rounding, text cleanup, conditionals, nested functions.
Tips
- MAP is only available in Microsoft 365 / Excel 2024+ and Google Sheets; it is not supported in older Excel versions or HyperFormula-based environments.
- When mapping two ranges, the arrays must have identical dimensions — a mismatch returns a #VALUE! error.
- For simple per-cell math (multiply by 2, add tax rate), ARRAYFORMULA in Sheets or a plain array formula in Excel is often shorter; reserve MAP for logic that needs a named variable.
Working on a sheet you inherited? Run the Auditor on the whole file first — it flags every #REF!, #N/A, broken column pattern, and inconsistent formula in seconds, free, no signup.
Frequently asked
Can MAP return multiple columns?
Not directly from a single MAP call. Use HSTACK or CHOOSECOLS to combine multiple MAP results side by side.
Does MAP work with non-contiguous ranges?
No. MAP requires contiguous range references or array constants. Use VSTACK to combine non-contiguous ranges before passing them to MAP.
What is the difference between MAP and ARRAYFORMULA?
ARRAYFORMULA (Sheets) broadcasts a formula across a range but does not support custom LAMBDA logic. MAP lets you write arbitrary per-element logic with named parameters, which is more expressive.
More on LAMBDA (reusable formulas)
See all →Reference
LAMBDA
Defines a reusable custom function using named parameters and a formula body, letting you call it like any built-in function — no VBA or scripts needed.
How-toHow to write a recursive LAMBDA function
Build self-referencing recursive functions using LAMBDA with the Excel Name Manager or Google Sheets named functions — for tasks like factorial, Fibonacci, or tree traversal.
How-toHow to use LAMBDA with REDUCE for cumulative math
Use REDUCE with a LAMBDA to fold a range into a single accumulated value in Excel 365 and Google Sheets — perfect for running totals, products, or custom aggregations.
How-toHow to use LAMBDA with SCAN for a running balance
Use SCAN with a LAMBDA to compute a running balance or cumulative total in Excel 365 and Google Sheets, returning one result per row without helper columns.
How-toHow to use BYROW and BYCOL to apply a LAMBDA per row or column
BYROW and BYCOL apply a LAMBDA to each row or column of a range in Excel 365 and Google Sheets, returning a per-row or per-column result without helper rows or columns.
How-toHow to use MAKEARRAY with LAMBDA
MAKEARRAY generates a 2-D array of any size in Excel 365 and Google Sheets by calling a LAMBDA with each row and column index, letting you build multiplication tables, calendars, or computed grids.