Lookup & Reference
Group arbitrary text values into P&L roll-up buckets (XLOOKUP mapping table)
Map dozens of vendor names or raw status labels into clean summary categories—with an automatic "Other" catch-all—using XLOOKUP and SUMIFS.
Replace 12-level nested IFs with an auditable 2-column mapping table!
A separate mapping table makes category rules easier to inspect and edit than a long nested IF. The example uses four merchant mappings and five transactions.
XLOOKUP assigns known names to their mapped category and unknown nonempty names to Other. A fallback does not prove reconciliation: missing labels, duplicate keys, nonnumeric amounts and category changes still require review. These formulas use fixed ranges and ordinary case-insensitive matching.
| Merchant (A) | Spend $ (B) | Mapped Roll-Up Bucket (C) | Bucket Roll-Up Total |
|---|---|---|---|
| AWS ($1,200) & Figma ($300) | $1,500 | "Cloud & SaaS" | $1,500 |
| Delta Air ($650) & Hilton ($450) | $1,100 | "Travel" | $1,100 |
| City Cab ($80 — not in map!) | $80 | "Other" (XLOOKUP 4th arg) | $80 (Grand Total = $2,680!) |
Map each raw text item to its roll-up group with "Other" fallback
A missing merchant is flagged separately. Matching map keys must be unique and their category must be populated; conflicting or empty mappings are flagged for review. The example assumes merchant IDs do not contain COUNTIF wildcard characters (*, ? or ~); use a literal-match lookup policy for arbitrary identifiers.
=IF(COUNTBLANK(A2)>0,"Missing merchant",IF(COUNTIF($E$2:$E$5,A2)>1,"Review mapping",IF(COUNTIFS($E$2:$E$5,A2,$F$2:$F$5,"")>0,"Review mapping",XLOOKUP(A2,$E$2:$E$5,$F$2:$F$5,"Other"))))Summarize spend by roll-up bucket with SUMIFS
Aggregates B2:B6 wherever mapped bucket C2:C6 equals the requested P&L line.
Include Other, Missing merchant and Review mapping in reconciliation reports. SUMIFS ignores text amounts: validate numeric spending separately. Adding a map or transaction outside the demonstrated ranges requires extending the ranges.
=SUMIFS(B2:B6, C2:C6, "Cloud & SaaS")Try the example
Use the original values or change them to test your understanding.