# How to categorize transactions by keywords in Excel

Keep keywords and categories in a table and return the category of the first keyword found: =XLOOKUP(TRUE,ISNUMBER(SEARCH(keywords,A2)),categories,"Uncategorized").

**Excel:** `=XLOOKUP(TRUE,ISNUMBER(SEARCH($E$2:$E$7,A2)),$F$2:$F$7,"Uncategorized")` (Excel 2021, 2024 and Microsoft 365)

**Google Sheets:** `=ARRAYFORMULA(XLOOKUP(TRUE,ISNUMBER(SEARCH($E$2:$E$7,A2)),$F$2:$F$7,"Uncategorized"))`

## Example

Sample data (row 1 is the header):

| A | B | C | D | E | F |
| --- | --- | --- | --- | --- | --- |
| Description | Amount | Category |  | Keyword | Category |
| UBER *TRIP 8812 | 23.4 |  |  | uber eats | Meals |
| AMAZON WEB SERVICES | 112.9 |  |  | uber | Travel |
| DELTA AIR 0062 | 418 |  |  | amazon web | Software |
| STARBUCKS #2231 | 6.75 |  |  | delta air | Travel |
| ADOBE CREATIVE CLD | 59.99 |  |  | starbucks | Meals |
| CHIPOTLE 1187 | 14.2 |  |  | adobe | Software |
| UBER EATS 9921 | 31.6 |  |  |  |  |

Formula in C2, filled to C8: `=XLOOKUP(TRUE,ISNUMBER(SEARCH($E$2:$E$7,A2)),$F$2:$F$7,"Uncategorized")`

Result: Travel; Software; Travel; Meals; Software; Uncategorized; Meals

## How it works

SEARCH checks the description for every keyword at once, and ISNUMBER marks the ones found as TRUE. XLOOKUP finds the first TRUE and returns the category from the same row. Because it takes the first match, "uber eats" sits above "uber" in the table, so Uber Eats is a meal and an Uber trip is travel.

## Other ways

- `=IFERROR(INDEX($F$2:$F$7,MATCH(TRUE,ISNUMBER(SEARCH($E$2:$E$7,A2)),0)),"Uncategorized")`: For Excel 2019 and earlier. In Excel 2016 and older, confirm it with Ctrl+Shift+Enter.

## Common mistakes

- Order matters: put specific keywords above general ones, or "uber" claims every Uber Eats charge.
- An empty row inside the keyword range matches every description. Size the range to the filled rows, or use an Excel Table.
- Very short keywords ("air") match inside unrelated words. Use distinctive text.

## Questions

**How do I total spending per category?**

SUMIF on the new column: `=SUMIF(C2:C8,"Travel",B2:B8)`.

**Can the match be case-sensitive?**

Use FIND instead of SEARCH.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/categorize-text-by-keywords