MAP applies one rule to every value in a range and returns the results in a block that spills down the sheet. With LAMBDA, you can flag every transaction above its own category limit in a single formula, rather than copying a formula down 3,000 rows. MAP needs Microsoft 365; Excel 2021 and older do not have it.
What problem are we solving?
Imagine a transactions sheet with categories in A2:A3000 and amounts in B2:B3000. Beside it, a small limits table sits in F2:G9 with categories in F and a per-transaction limit in G. For example, Food is ₹6,000, Travel ₹3,000 and Shopping ₹5,000. You want a flag beside each transaction that exceeds the limit for its own category.
The old way: copy a formula down
In C2 you would write a lookup with an IF, then drag it down to row 3000:
=IF(B2="","",IF(B2>IFERROR(INDEX($G$2:$G$9,MATCH(A2,$F$2:$F$9,0)),1E+99),"Over limit",""))
That works. But 3,000 copies of a formula is 3,000 places for something to break: someone types over one, sorts the data badly, or inserts rows that do not inherit the formula.
The MAP way: one formula that spills
MAP takes one or more ranges and then a LAMBDA, whose inputs line up with those ranges in order. Type this once in C2:
=MAP($A$2:$A$3000,$B$2:$B$3000,LAMBDA(cat,amt,IF(amt="","",IF(amt>IFERROR(INDEX($G$2:$G$9,MATCH(cat,$F$2:$F$9,0)),1E+99),"Over limit",""))))
Here cat receives each category and amt receives the matching amount. The LAMBDA is the same rule as before, written once. If a category has no entry in the limits table, IFERROR supplies a huge limit, so it is never flagged.
A few rows to check against
| Category | Amount | Limit | Flag |
|---|---|---|---|
| Food | ₹7,500 | ₹6,000 | Over limit |
| Travel | ₹2,200 | ₹3,000 | No flag |
| Shopping | ₹5,000 | ₹5,000 | No flag |
| Gifts | ₹9,000 | not in the table | No flag |
Shopping at exactly ₹5,000 is not flagged because the rule is greater than, not greater than or equal to. Change the sign to >= if you want it flagged. Gifts has no limit listed, so it passes, which may or may not be what you want; add a row to the limits table to include it.
What are the catches?
- Spill space: the cells below C2 must be empty. Anything there causes a #SPILL! error.
- Fixed range: $A$2:$A$3000 will not include row 3001. Convert your data to an Excel Table and use its column names, or extend the range.
- Do not sort or filter the output column on its own; sort the source data instead.
- One value per call: the LAMBDA must return a single value for each pair, not a list.
How many were flagged?
A second cell can count the flags without any helper: =COUNTIF($C$2:$C$3000,"Over limit"). To total the amounts over the limit, use =SUMIFS($B$2:$B$3000,$C$2:$C$3000,"Over limit"). In the small example above, only Food is flagged, so the count is 1 and the total is ₹7,500.
A variation: warn before the limit is crossed
A second flag for being close is often more useful than one for being over. Use LET to look up the limit once, then test two thresholds, here 80 percent of the limit:
=MAP($A$2:$A$3000,$B$2:$B$3000,LAMBDA(cat,amt,LET(lim,IFERROR(INDEX($G$2:$G$9,MATCH(cat,$F$2:$F$9,0)),1E+99),IF(amt="","",IF(amt>lim,"Over limit",IF(amt>=0.8*lim,"Close",""))))))
With a Food limit of ₹6,000, 80 percent is ₹4,800. A Food payment of ₹5,000 shows Close, one of ₹7,500 shows Over limit, and one of ₹2,000 shows nothing. Change 0.8 to any share you like.
Where this fits
A per-transaction limit is a blunt tool. It catches the unusually large payment, not the slow drip of many small ones. For that, monthly totals by category are better. If you find yourself maintaining limits, flags and monthly totals across several accounts, a tracker with budgets and category reports is the next step; the sheet taught you what you want to track.
Checklist before you rely on it
- Check that the limits table has every category you use, spelled the same way.
- Test with three rows: one clearly over, one exactly on the limit, one under.
- Make sure the cells below the formula are empty.
- Note in the sheet that this needs Microsoft 365.
Useful ideas start with a clearer picture of your own spending.
Explore your reports

