# How to categorize text based on keywords in Excel

Return the category of the first keyword found: =XLOOKUP(TRUE,ISNUMBER(SEARCH($D$2:$D$6,A2)),$E$2:$E$6,"Other").

**Excel:** `=XLOOKUP(TRUE,ISNUMBER(SEARCH($D$2:$D$6,A2)),$E$2:$E$6,"Other")` (Excel 2021, 2024 and Microsoft 365)

**Google Sheets:** `=ARRAYFORMULA(IFERROR(INDEX($E$2:$E$6,MATCH(TRUE,ISNUMBER(SEARCH($D$2:$D$6,A2)),0)),"Other"))`

## Example

Sample data (row 1 is the header):

| A | B | C | D | E |
| --- | --- | --- | --- | --- |
| Transaction | Category |  | Keyword | Category |
| UBER *TRIP 4471 |  |  | uber | Travel |
| Starbucks #2231 |  |  | starbucks | Meals |
| AMAZON MKTPL ORDER |  |  | amazon | Supplies |
| Delta Air 0062 |  |  | delta | Travel |
| Office Depot 118 |  |  | depot | Supplies |
| Venmo transfer |  |  |  |  |

Formula in B2, filled to B7: `=XLOOKUP(TRUE,ISNUMBER(SEARCH($D$2:$D$6,A2)),$E$2:$E$6,"Other")`

Result: Travel; Meals; Supplies; Travel; Supplies; Other

## How it works

`ISNUMBER(SEARCH($D$2:$D$6,A2))` builds a list of TRUE and FALSE, one per keyword: TRUE where the keyword appears in the transaction. XLOOKUP finds the first TRUE and returns the category on the same row, or "Other" when nothing matches.

## Other ways

- `=IFERROR(INDEX($E$2:$E$6,MATCH(TRUE,ISNUMBER(SEARCH($D$2:$D$6,A2)),0)),"Other")`: Without XLOOKUP. In Excel 2019 and earlier, confirm it with Ctrl+Shift+Enter.

## Common mistakes

- The first keyword in the table wins. Put specific keywords above general ones ("uber eats" above "uber") so they are found first.
- Short keywords match inside other words: "delta" also matches "Deltaville Hardware". Use longer keywords or add spaces around them.
- An empty row in the keyword table matches every transaction. Keep the range exact, or use a Table so it grows with the list.

## Questions

**How do I list every matching category?**

Filter instead of looking up: `=TEXTJOIN(", ",TRUE,UNIQUE(FILTER($E$2:$E$6,ISNUMBER(SEARCH($D$2:$D$6,A2)),"")))`.

**How do I make the keyword match 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-based-on-keywords