Text
Categorize bank or expense descriptions by keyword rules
Map noisy merchant strings like "Card #44 UBER *TRIP" into clean accounting categories using IFS, ISNUMBER, and SEARCH.
Substring keyword matching with ISNUMBER(SEARCH(...))
Exact lookups like VLOOKUP fail on bank feeds because every statement line adds card numbers, store IDs, or cities around the merchant name.
SEARCH(keyword, description) returns the starting character number when found (case-insensitively) or #VALUE! when absent. Wrapping SEARCH in ISNUMBER converts that result into TRUE/FALSE for clean left-to-right IFS rule evaluation.
Require nonempty keywords: an empty SEARCH term can match every description. These are substring rules, so review ambiguous matches and false positives before using the categories. The labels illustrate a workflow rather than prescribe accounting or tax treatment.
| Description (A) | Matched Keyword | Assigned Category |
|---|---|---|
| Card #44 UBER *TRIP | UBER | Travel |
| Monthly AWS Cloud Services | AWS | Hosting |
| Local Bakery Catering | (none) | Uncategorized |
Ordered keyword-rule formula
Place more specific keywords before broader ones in IFS, and end with TRUE, "Uncategorized" so unmatched merchants never return #N/A.
=IFS(ISNUMBER(SEARCH($B$2, A2)), $C$2, ISNUMBER(SEARCH($D$2, A2)), $E$2, ISNUMBER(SEARCH($F$2, A2)), $G$2, TRUE, "Uncategorized")Verify fallback and new merchant match
"Local Bakery Catering" matches none of UBER, AWS, or STAPLES and falls through to "Uncategorized".
=IFS(ISNUMBER(SEARCH($B$2, A4)), $C$2, ISNUMBER(SEARCH($D$2, A4)), $E$2, ISNUMBER(SEARCH($F$2, A4)), $G$2, TRUE, "Uncategorized")Try the example
Use the original values or change them to test your understanding.