Spreadsheets

MAP and LAMBDA in Google Sheets: apply a rule to every row

Use MAP with LAMBDA in Google Sheets to label every transaction with a spending tier in one formula, and learn when it beats ARRAYFORMULA.

Editorial illustration for MAP and LAMBDA in Google Sheets: apply a rule to every row

In Google Sheets, MAP takes a range and a LAMBDA, runs the LAMBDA on each cell, and returns one result per row. To tag every transaction as Small, Medium or Large in one formula, type =MAP(C2:C500,LAMBDA(amt,IFS(amt="","",amt>=5000,"Large",amt>=1000,"Medium",TRUE,"Small"))) in D2. One formula, no dragging, and it keeps working as rows are added up to row 500.

What do the MAP and LAMBDA parts mean?

LAMBDA(amt, ...) is a small unnamed function with one input called amt. MAP feeds it each value from C2:C500 in turn and stacks the answers into a column. Say your thresholds are your own: under ₹1,000 is Small, ₹1,000 to ₹4,999 is Medium, ₹5,000 and above is Large. The IFS reads top to bottom and stops at the first true test, so the order matters.

AmountFirst true testResult
blank cellamt=""blank
₹640TRUE (nothing above matched)Small
₹1,000amt>=1000Medium
₹4,999amt>=1000Medium
₹18,000amt>=5000Large

Put the header Tier in D1 as plain text and keep column D below it empty, or the formula shows #REF! with a message that the array result was not expanded because it would overwrite data.

Is MAP clearer than ARRAYFORMULA?

For a simple test, the older way works fine: =ARRAYFORMULA(IF(C2:C500="","",IF(C2:C500>=5000,"Large",IF(C2:C500>=1000,"Medium","Small")))). The trouble is that every reference to the range has to be written out each time, and some functions refuse to work row by row inside ARRAYFORMULA. MAP names the value once and lets you write the rule as you would for a single cell.

  • Use ARRAYFORMULA when the rule is one plain comparison or arithmetic on whole columns.
  • Use MAP when the rule has several steps, nested IFs you want to read, or a function that must run per row.
  • Use MAP with two ranges when the rule needs two columns: =MAP(B2:B500,C2:C500,LAMBDA(cat,amt,IF(AND(cat="Shopping",amt>=5000),"Review","")))

That last formula flags big shopping rows for a second look. The LAMBDA lists one name per range, in the same order as the ranges.

Can it do a per-row calculation ARRAYFORMULA cannot?

Yes. Suppose A holds merchant names and you want the total spent at each from a longer list in F2:G500 (merchant in F, amount in G):

  • =MAP(A2:A20,LAMBDA(m,SUMIF($F$2:$F$500,m,$G$2:$G$500)))

SUMIF is happy to work with one merchant at a time, and MAP supplies that one at a time. For a rule across columns in each row, BYROW does the same job: =BYROW(C2:E100,LAMBDA(r,MAX(r))) gives the biggest of three monthly figures in every row. Both functions return a spilled column, so leave space below.

Where does it go wrong?

  • Blank rows: MAP runs on empty cells too. Test for blanks first, as the first IFS line above does.
  • Text amounts: a figure stored as text fails the number test. Wrap with VALUE inside the LAMBDA only if the cells really are text.
  • The LAMBDA must return one value per call. If it returns a list, you get an error, not a grid.
  • Names clash: do not call the input amount if you also have a named range called amount; use short names like amt.
  • Very long ranges slow down. Point MAP at the rows you use, not the whole column.

What would you do with the tier column?

  1. Filter for Large and check each row against a receipt or the statement line.
  2. Count tiers with =COUNTIF(D2:D500,"Large") and track whether big purchases are creeping up.
  3. Add the tier to a SUMIFS so you can see the total spent in Small transactions, which is often where small leaks hide.

MAP is a good tool once a rule outgrows a single IF. If you find yourself rewriting rules for merchants rather than amounts every month, SpendixAI lets you set your own categorisation rules and reuses your past corrections, so the sheet logic can stay for the reports you care about.

SPENDIXAI JOURNAL

Useful ideas start with a clearer picture of your own spending.

Explore your reports