Text & Cleaning
Normalize Noisy Vendor & Bank-Feed Strings via Keyword Mapping (SEARCH & IFS)
Map messy corporate card and bank-feed descriptions containing terminal IDs, asterisks, and city suffixes to canonical vendor names using ordered ISNUMBER(SEARCH(...)) rules and SUMIF spend rollups.
Deterministic token matching vs opaque probabilistic fuzzy joins
Raw bank and credit-card exports rarely match clean ERP vendor names: "AWS EMEA *4491 DUBLIN", "AMZN WEB SERVICES", and "STRIPE*CLOUDFLARE INC" defeat exact VLOOKUP/XLOOKUP because every transaction appends a unique reference number or city.
Instead of opaque similarity scores that can misclassify short acronyms, an ordered keyword mapping table D2:E4 paired with ISNUMBER(SEARCH(token, rawText)) maps each noisy string to a canonical vendor ("Cloud Infrastructure", "CDN & Edge", "Courier Freight") with 100% auditability while routing unmatched rows to "Unclassified" for manual review.
| Raw Bank Description | Matched Keyword | Canonical Vendor | Amount |
|---|---|---|---|
| ACH DEBIT AWS EMEA #4491 | AWS | Amazon Web Services | $420 |
| POS STRIPE*CLOUDFLARE | CLOUDFLARE | Cloudflare Inc. | $95 |
| WIRE DHL EXPRESS FRA | DHL | DHL Logistics | $180 |
| CARD AWS US-EAST #8812 | AWS | Amazon Web Services | $310 |
| POS LOCAL BAKERY 09 | (None) | Unclassified | $35 |
Keyword-Driven Canonical Vendor Classifier
Tests noisy memo A2 against ordered keyword tokens $D$2:$D$4 and returns the canonical vendor name in $E$2:$E$4 or "Unclassified".
=IFS(ISNUMBER(SEARCH($D$2, A2)), $E$2, ISNUMBER(SEARCH($D$3, A2)), $E$3, ISNUMBER(SEARCH($D$4, A2)), $E$4, TRUE, "Unclassified")Normalized Vendor Spend Rollup & Unclassified Audit
Totals spend in B2:B6 mapped to a target canonical vendor in C2:C6 via SUMIF.
=SUMIF(C2:C6, "Amazon Web Services", B2:B6)Try the example
Use the original values or change them to test your understanding.