# How to assign a category when a cell contains a keyword from a list

Search the cell for every keyword and return the category of the first one found: `=XLOOKUP(TRUE,ISNUMBER(SEARCH($E$2:$E$8,A2)),$F$2:$F$8,"Other")`.

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

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C | D | E | F |
| --- | --- | --- | --- | --- | --- |
| Description | Amount | Category |  | Keyword | Category |
| UBER *TRIP 8841 | 23.4 |  |  | uber | Transport |
| STARBUCKS #10293 | 6.85 |  |  | lyft | Transport |
| SHELL OIL 5571 | 48.2 |  |  | starbucks | Coffee |
| NETFLIX.COM | 15.49 |  |  | shell | Fuel |
| WHOLE FOODS MKT 102 | 112.37 |  |  | netflix | Subscriptions |
| LYFT RIDE 3390 | 31.9 |  |  | spotify | Subscriptions |
| SPOTIFY USA | 11.99 |  |  | whole foods | Groceries |
| HARDWARE HANK | 64.15 |  |  |  |  |

Formula in C2, filled to C9: `=XLOOKUP(TRUE,ISNUMBER(SEARCH($E$2:$E$8,A2)),$F$2:$F$8,"Other")`

Result: Transport; Coffee; Fuel; Subscriptions; Groceries; Transport; Subscriptions; Other

## How it works

`SEARCH($E$2:$E$8,A2)` looks for every keyword in the description at once and returns a position where it is found and #VALUE! where it isn't. ISNUMBER turns that into TRUE and FALSE, and XLOOKUP returns the category beside the first TRUE. SEARCH ignores case, so "uber" finds "UBER".

## Other ways

- `=IFERROR(INDEX($F$2:$F$8,MATCH(TRUE,ISNUMBER(SEARCH($E$2:$E$8,A2)),0)),"Other")`: For Excel 2019 and earlier (Ctrl+Shift+Enter in 2016 and older).

## Common mistakes

- Short keywords match inside other words: "shell" would also tag "SHELLEY'S BAKERY". Make keywords specific, or put the more specific ones higher in the list.
- When two keywords match, the one higher in the keyword list wins.
- Lock the keyword ranges with `$` before filling down.

## Questions

**How do I make the keyword match case-sensitive?**

Use FIND instead of SEARCH.

**How do I return the matched keyword itself?**

Return the keyword column: `=XLOOKUP(TRUE,ISNUMBER(SEARCH($E$2:$E$8,A2)),$E$2:$E$8,"")`.

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/assign-category-by-keyword