Instead of editing rows by hand, write a small keyword table once and let one formula assign a category to every row containing a keyword. Anything it cannot match is labelled Review, so you only touch the leftovers. This works for 200 rows or 2,000.
How should the keyword table look?
In columns G and H, list a word that appears in the description and the category it should map to.
| Keyword (column G) | Category (column H) |
|---|---|
| grocer | Groceries |
| rail | Travel |
| cinema | Entertainment |
| pharmacy | Health |
| energy | Bills |
Keep the keywords short and lower case. The formula below ignores upper and lower case.
What is the formula?
With descriptions in column B and the keyword table in G2:H40, put this in C2 and fill down. It needs Excel 365 or 2021, or Google Sheets with ARRAYFORMULA as shown in the second line.
- Excel 365/2021: =IFERROR(INDEX($H$2:$H$40, MATCH(TRUE, ISNUMBER(SEARCH($G$2:$G$40, B2)), 0)), "Review")
- Google Sheets: =IFERROR(INDEX($H$2:$H$40, MATCH(TRUE, ARRAYFORMULA(ISNUMBER(SEARCH($G$2:$G$40, B2))), 0)), "Review")
SEARCH looks for each keyword inside the description, ISNUMBER turns a hit into TRUE, MATCH finds the first TRUE, and INDEX returns the matching category. In older Excel versions the formula needs to be confirmed with Ctrl, Shift and Enter. If nothing matches, IFERROR returns Review.
How do you handle what the formula missed?
Filter column C for Review. Suppose 200 rows give 143 matches and 57 reviews. Pick the most common unmatched description, add a keyword for it in the table and every similar row is fixed at once. Two or three additions can clear most of the 57.
What mistakes should you avoid?
- A keyword that is too short, like car, will match carpet and card payment alike.
- Two keywords that both match a row: the first in the table wins, so order the specific ones above the general ones.
- Keywords with trailing spaces, which silently fail.
How do you check the result?
Use COUNTIF to count each category: =COUNTIF($C$2:$C$201, "Groceries"). The counts across all categories plus Review should equal 200. Compare the sum of amounts before and after as well, since categorising must not change money.
How do you tidy the keyword table over time?
Review the table every few months. Keywords that no longer appear can go, and keywords that cause wrong matches should be made longer: change pharm to pharmacy if it also matches something else. Add a column with the date you added each keyword so you can see what the table has learned. A tidy table of thirty entries is far more useful than a sprawling table of three hundred.
When the table grows, split it into two: one for exact merchants and one for general words. Check the merchants table first and the general words second, which prevents a loose keyword from overriding a precise one. Name them clearly so that next month you remember which does what.
A last tip on testing: before you fill the formula down all 200 rows, paste it into five rows whose answers you already know and check each result by eye. If a pharmacy row comes back as Bills, the keyword order is wrong. It is far cheaper to find that on five rows than after filling the column, and the same five test rows can be reused every month as a quick regression check.
Where does an app help?
SpendixAI lets you set your own rules and bulk edit or delete transactions, so corrected merchants carry forward. It is built around India and INR, so for pounds the lookup above is the route.
Useful ideas start with a clearer picture of your own spending.
Explore your reports


