MAP function
Quick answer
MAP runs a LAMBDA on each value of an array, or on matching values from several arrays, and returns an array of the same size.
- Syntax
=MAP(array1, [array2], ..., lambda)- Most common use
=MAP(B2:B4,C2:C4,LAMBDA(s,t,IF(AND(s>=t,s>10000),"Bonus","-")))returns Bonus (AND checked row by row)
Use it for value-by-value logic that won't spill on its own, such as IF with AND or OR across two columns, which otherwise collapses to one TRUE or FALSE.
- Available in
- Excel 2024 and Microsoft 365
- Category
- Dynamic array functions
- Returns
- An array the same size as the inputs.
- Google Sheets
- Available in Google Sheets.
Syntax
=MAP(array1, [array2], ..., lambda)
| Argument | Required? | What it is |
|---|---|---|
array1 | Required | The first array or range. |
array2 | Optional | Optional: more arrays the same size, whose values are passed alongside. |
lambda | Required | A LAMBDA with one input per array. |
Examples
Each result below is calculated from the sample sheet by our formula engine, the same one that grades the practice.
| Cell | Formula | Result | What it shows |
|---|---|---|---|
| D2 | =MAP(B2:B4,C2:C4,LAMBDA(s,t,IF(AND(s>=t,s>10000),"Bonus","-"))) | Bonusspills 3 × 1 | AND checked row by row |
| E2 | =MAP(B2:B4,LAMBDA(s,ROUND(s/1000,1))) | 12.5spills 3 × 1 | Each sale in thousands |
| F2 | =SUM(MAP(B2:B4,C2:C4,LAMBDA(s,t,MAX(0,s-t)))) | $2,700 | Total sales above target |
Try it live
This is the sample sheet. Change any number or text, or edit a formula, and the results update.
Show the sample data as a table
| Rep | Sales | Target | Result | ||
| Ana | $12,500 | $10,000 | Bonus | 12.5 | $2,700 |
| Raj | $8,800 | $9,000 | - | 8.8 | |
| Lee | $15,200 | $15,000 | Bonus | 15.2 |
Practice MAP
Graded instantly, then re-checked on changed data.
Flag both conditions
D2, use MAP to return "Review" for each expense where the amount in B2:B7 is over 500 and the receipt column C2:C7 says "No", and "OK" otherwise, in one formula.Common mistakes
| What you see | Why, and how to fix it |
|---|---|
| One TRUE or FALSE instead of one per row | That's AND or OR without MAP: they combine the whole array. MAP applies them to each row. |
| #VALUE! | The LAMBDA has a different number of inputs from the number of arrays. |
| #N/A in some results | The arrays aren't the same size. |
Tips
- When each row needs AND or OR, MAP keeps them per row: =MAP(A2:A9,B2:B9,LAMBDA(a,b,AND(a>0,b>0))).
Questions
How do I use IF with AND on a whole column at once?
=MAP(B2:B100,C2:C100,LAMBDA(s,t,IF(AND(s>=t,s>0),"Yes","No"))) checks AND row by row.
What is the difference between MAP and BYROW?
MAP works on single values; BYROW hands the LAMBDA a whole row at a time.
Updated Checked by running: every formula on this page was run by our formula engine on .