Do these 3 things before closing this tab:
1Repair Windows errors before they cause bigger problems2Fix the driver behind crashes, sound loss and screen glitches3Clear out junk files and repair common Windows errorsExcel’s MAP function runs one custom calculation on every value in a range or array and returns all of the results together in a single formula. You write the per-value logic once inside a LAMBDA, and Excel repeats it for each element, so you do not need a helper column or a formula copied down the sheet. Microsoft defines it as returning “an array formed by mapping each value in the array(s) to a new value by applying a LAMBDA to create a new value.”
How MAP is built
The basic pattern is =MAP(array1, lambda_or_array<#>). The LAMBDA always comes last, and it needs one parameter for every array you pass in. Excel hands each element of each array to the matching parameter, runs the LAMBDA body, and collects the outputs.
Before you build a MAP formula, check these four things:
- Each array you pass has a matching parameter in the LAMBDA (one array needs one parameter, two arrays need two).
- The LAMBDA is the final argument.
- The parentheses and argument separators follow your Excel locale. A formula copied from a US-English example may need commas changed to semicolons in some regional settings.
- The result is meant to be one value per input element. If you need one value per row, column or running total, a different helper is usually the better tool (see the comparison below).
Example 1: Transform one range
Microsoft’s first example applies one LAMBDA to a single range:
Quick wins for a faster PC:
Fix the driver behind crashes, sound loss and screen glitchesFind Drivers →Clear out junk files and repair common Windows errorsFree Scan →#1 Best Overall
- The Microsoft Office 365 Bible: The Most Updated and Complete Guide to Excel, Word, PowerPoint, Outlook, OneNote, OneDrive, Teams, Access, and Publisher from Beginners to Advanced
- ABIS BOOK
=MAP(A1:C2, LAMBDA(a, IF(a>4,a*a,a)))Excel takes each of the six cells in A1:C2 and passes its value to the parameter a. If the value is greater than 4, the LAMBDA returns its square. Otherwise it returns the value unchanged. The output is an array of mapped values, one per input cell. The reader does not have to write a separate formula in each cell, and changing the threshold or the operation means editing one LAMBDA.
Example 2: Test two table columns side by side
MAP can work across two arrays at once. Microsoft’s table-column example looks like this:
=MAP(TableA[Col1], TableA[Col2], LAMBDA(a,b,AND(a,b)))Each call to the LAMBDA receives the value from Col1 and the value from Col2 in the same row. The AND test returns TRUE only when both values evaluate TRUE. Because the two arrays are read position by position, they need to be the same length and aligned to the same rows.
Example 3: Use MAP inside FILTER
The most useful pattern for many workbooks is to let MAP generate a TRUE/FALSE test and pass that test to FILTER. Microsoft’s example is:
Free tools Windows power users keep installed
One-click scans. No signup required.
=FILTER(D2:E11,MAP(D2:D11,E2:E11,LAMBDA(s,c,AND(s="Large",c="Red"))))For each size and color pair, the LAMBDA returns TRUE only when the size is “Large” and the color is “Red”. FILTER then returns the rows in D2:E11 where the test is TRUE. This turns a per-element condition into a row selection without a helper column.
Choosing between MAP and the other LAMBDA helpers
MAP belongs to a family of LAMBDA helper functions. The difference between them is the shape of the answer you need, not which one is more powerful.
Rank #4
| Function | What it returns | Use it when |
|---|---|---|
| MAP | An array with one mapped value per input element | Every item needs the same transformation or test |
| BYROW | One result for each row | You want to summarize or test each row as a unit |
| BYCOL | One result for each column | You want to summarize or test each column as a unit |
| REDUCE | One accumulated value | You need to combine all values into a single result |
| SCAN | An array of intermediate accumulated results | You need a running total or each step of an accumulation |
These roles are taken from Microsoft’s function reference. Confirm the exact syntax of BYROW, BYCOL, REDUCE and SCAN in your own Excel edition before you build a workbook around any of them, because this article does not walk through their arguments.
Which Excel versions support MAP
Microsoft’s MAP function page lists Excel for Microsoft 365 and Excel 2024, each for Windows and Mac. Microsoft’s alphabetical function list tags MAP with the version marker “2024,” which indicates the Excel release in which the function was introduced. The page does not list Excel 2021 or earlier, so do not assume a MAP formula will calculate in those versions. If a workbook is shared with colleagues on older releases, test it on their version first.
Troubleshooting MAP and LAMBDA errors
#VALUE! with the message “Incorrect Parameters”
Microsoft says an invalid LAMBDA or a wrong parameter count returns #VALUE!. The usual causes are a LAMBDA with fewer or more parameters than the arrays passed to MAP, or a LAMBDA that is not the last argument. Count the arrays, count the parameters, and make sure they match one for one.
#CALC! when a LAMBDA is typed into a cell
A LAMBDA placed in a cell without being called returns #CALC!. A LAMBDA is a function definition, so it needs arguments before it produces a value. Add sample arguments to test it (see the steps below).
#NUM! from excessive recursion
Microsoft notes that excessive circular recursion inside a LAMBDA may return #NUM!. If a LAMBDA calls itself, make sure it has a stopping condition that is reached within a reasonable number of steps.
Test a LAMBDA and reuse it
Microsoft’s LAMBDA guidance recommends testing the function in a cell first, then saving a working version as a named function. The steps are:
- In a blank cell, enter the LAMBDA with a sample argument, for example
=LAMBDA(a, IF(a>4,a*a,a))(5). The expected result is 25, because 5 is greater than 4 and its square is 25. - Try a value that should be returned unchanged, such as
=LAMBDA(a, IF(a>4,a*a,a))(3). The expected result is 3. - Go to the Formulas tab and select Name Manager.
- Select New, enter a name such as SquareIfOver4, and paste the LAMBDA definition (without the trailing sample argument) into the Refers to box.
- Use the name in MAP:
=MAP(A1:C2, SquareIfOver4). Once the name exists, you do not need to repeat the LAMBDA in every formula.
”
The Bottom Line
Use MAP when every element in an array needs the same transformation or test and you want the results returned together. For per-row or per-column summaries, use BYROW or BYCOL; for a single accumulated answer or a running total, use REDUCE or SCAN. MAP is a precise tool for element-level work, not a replacement for ordinary formulas.
Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
