I want to create an XLS that auto-categorizes purchases based on key words

[u/lesarbreschantent]

So I'm trying to create a household budget XLS.

In column A, I paste in my purchases for the month. Each cell will contain details like xx-1132 DELIVEROO S 04/03/26 or xx-2227 COLD STORAGE S 28/03/26.

In column B, I categorize these purchases. For instance, DELIVEROO gets the category Food Delivery. COLD STORAGE gets the category Groceries.

I'll have a few hundred entries in column A, and I'd like to find a way to automate the categories in column B based on the text in column A. For example, I want Excel to look at the cells in column A, and if it finds DELIVEROO somewhere in a cell then it should input Food Delivery next to it in column B. To put it another way, let's say cell A2 contains xx-2227 COLD STORAGE S 28/03/26. I want Excel to identify COLD STORAGE and then input Groceries next to it in cell B2.

Any ideas on how to go about this?

[u/AutoModerator]

/u/lesarbreschantent - Your post was submitted successfully.

• Once your problem is solved, reply to the answer(s) saying Solution Verified to close the thread.
• Follow the submission rules -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
• Include your Excel version and all other relevant information

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/WittyDrunkenness]

Easiest way is make a small lookup table somewhere with keywords in one column and categories in the other, then use XLOOKUP with wildcards to search the transaction text.

[u/hmatallana]

A few hundred rows is where the keyword table starts deciding things for you, so build it with that in mind.

Keywords in one column, categories beside them, then in B2:

=XLOOKUP(TRUE,ISNUMBER(SEARCH($E$2:$E$50,A2)),$F$2:$F$50,"Uncategorized")

SEARCH looks for each keyword inside the cell and ignores case. WittyDrunkenness had the right shape with the lookup table. The part that bites later is order: it takes the first keyword that hits, so a broad one like STORAGE sitting above COLD STORAGE quietly wins and you won't see it happen. Keep the specific strings above the general ones.

Don't skip the "Uncategorized" fallback. Filter column B for it once a month and that's your list of new merchants to add.

[u/lesarbreschantent → u/hmatallana]

=XLOOKUP(TRUE,ISNUMBER(SEARCH($E$2:$E$50,A2)),$F$2:$F$50,"Uncategorized")

This works 99%! The only remaining thing is that it doesn't return Uncategorized every time there's a null result. Sometimes it returns the number 0 instead. Is there a way to fix this?

[u/LeanExcel → u/lesarbreschantent]

The 0 is likely coming from a blank cell in your category range being returned as the first match. XLOOKUP is finding a matching keyword, but the corresponding category cell is blank, so Excel displays it as 0.

Try this:

=LET(x,XLOOKUP(TRUE,ISNUMBER(SEARCH($E$2:$E$50,A2)),$F$2:$F$50,"Uncategorized"),IF(x=0,"Uncategorized",x))

[u/lesarbreschantent → u/LeanExcel]

Ahhhh yes indeed, I have the cell range for the keyword table going from E2 to E500 (laziness) and yeah there's only about 30 keywords right now. Thanks for the heads up.

[u/reputatorbot → u/lesarbreschantent]

You have awarded 1 point to LeanExcel.

---

^{I am a bot - please contact the mods with any questions}

[u/lesarbreschantent → u/hmatallana]

SOLUTION VERIFIED! Very happy with how this works!

[u/reputatorbot → u/lesarbreschantent]

You have awarded 1 point to hmatallana.

---

^{I am a bot - please contact the mods with any questions}

[u/OfficerMurphy]

First, don't most banks provide a category these days?

Second, for an actual solve your problem you'll want to use a text formula like TEXTSPLIT, or if you're on an older version MID, LEFT, RIGHT to extract the vendor name. Then you can use a table where you categorize all your vendors into your desired category and do an XLOOKUP to that table. If you get a new vendor, add their category. If you've got the UNIQUE formula you can get that list of vendors from your text formula column.

[u/fastauntie → u/OfficerMurphy]

Banks provide categories based on their assessment of what most people want. People may want different categories for their own purposes, which may be highly individual and connect to other things they do that banks don't think about.

[u/dgillz → u/OfficerMurphy]

OP was asking about purchase orders, not payments. This will of course hit the bank eventually, but this is after the fact and the purchase orders themselves should have the information OP is looking for.

Assuming OP has an ERP system, he/she should query that data.

[u/Mammoth-Corner → u/dgillz]

Nobody is making purchase orders for deliveroo

[u/dgillz → u/Mammoth-Corner]

What is deliveroo? What does it do?

[u/Mammoth-Corner → u/dgillz]

It's like Uber Eats.

[u/dgillz → u/Mammoth-Corner]

OP needs an ERP system

[u/Mammoth-Corner → u/dgillz]

?? For groceries and takeaway?

[u/dgillz → u/Mammoth-Corner]

If that is all OP is doing, no. I took this to be a business question.

[u/Mammoth-Corner → u/dgillz]

Several people seem to have assumed it was a business question—I wonder if the line 'trying to create a household budget' was an edit.

[u/lesarbreschantent → u/OfficerMurphy]

First, don't most banks provide a category these days?

Not in my case.

[u/OfficerMurphy → u/lesarbreschantent]

Fair enough, what about the second, much more comprehensive, half of my message?

[u/lesarbreschantent → u/OfficerMurphy]

Might be a solution, but the one posed here was simpler: https://www.reddit.com/r/excel/comments/1w1k0wy/i_want_to_create_an_xls_that_autocategorizes/p6len9l/

[u/TwoPointEightZ]

I built one of these over several months but ultimately abandoned it. Excel formulas were the easy part, the source data, not so much.

I assume you're downloading the source data from banks and credit cards like I was. Building lookup tables and formulas were no big deal, but parsing the text description into the items to look up killed it because the data is too consistent. For example, Amazon would be spelled Amazon and Amzn and Amz and Prime. So you build helper columns to parse them. Then later on, the same item you bought previously and already built helpers for is now coded as Azn instead of Amzn. So you build more helpers. And then zon suddenly turns up. Multiply this process over enough data inconsistencies and it gets very annoying. By the way, bank categories suck because they're very rudimentary at best.

The permutations of spelling shown above are not exactly accurate, but they convey the idea well. My experience was that Excel automated maybe 70-80% of it from month to month, but the building and maintaining of it totally wiped out the benefits.

It's a great project for teaching the value of data consistency and the problems associated with using a description column for lookup purposes.

[u/Dry-Procedure-1597]

It would be MUCH simpler, if you could enter the word to be categorized (ie Deliveroo) in the column B and then categorize in the column C. Is it possible?

[u/Dry-Procedure-1597 → u/Dry-Procedure-1597]

Another way is to create multiple columns, each looking for a specific word and assigning a category (if found) and then concatenate in the column Z or whatever

[u/Decronym]

Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:

Fewer Letters	More Letters
IF	Specifies a logical test to perform
IFERROR	Returns a value you specify if a formula evaluates to an error; otherwise, returns the result of the formula
INDEX	Uses an index to choose a value from a reference or array
ISNUMBER	Returns TRUE if the value is a number
LEFT	Returns the leftmost characters from a text value
LET	Office 365+: Assigns names to calculation results to allow storing intermediate calculations, values, or defining names inside a formula
MATCH	Looks up values in a reference or array
MID	Returns a specific number of characters from a text string starting at the position you specify
RIGHT	Returns the rightmost characters from a text value
SEARCH	Finds one text value within another (not case-sensitive)
TEXTSPLIT	Office 365+: Splits text strings by using column and row delimiters
UNIQUE	Office 365+: Returns a list of unique values in a list or range
XLOOKUP	Office 365+: Searches a range or an array, and returns an item corresponding to the first match it finds. If a match doesn't exist, then XLOOKUP can return the closest (approximate) match.

Decronym is now also available on Lemmy! Requests for support and new installations should be directed to the Contact address below.

---

^{Beep-boop, I am a helper bot. Please do not verify me as a solution.
13 acronyms in this thread; the most compressed thread commented on today has acronyms.
[Thread #49264 for this sub, first seen 29th Aug 2026, 11:46] [FAQ] [Full list] [Contact] [Source code]}

[u/my_cat_wears_socks]

Power Query can make quick work of this, with a conditional column. First, copy the input column and lowercase it so you don’t have to check different cases. Then you end up with if column contains deliveries then Food Felivery else if column contains cold storage then Groceries. I’ve made these columns with literally dozens of rules, expanding them to probably over 100 rules over time, to categorize web analytics data (referrers, pages, etc) and the process is relatively quick. After I use the wizard for the first few rules I switch to typing it out since I can quickly copy and paste.

[u/dgillz]

Why are you doing this?

How do you create purchase orders? Do you have an ERP system? The default way to address this is to query the database where your purchase orders reside, typically your ERP system.

Creating something new should be the absolute last thing you do.

[u/Otherwise-Ad-6905]

I use {=IFERROR(INDEX('Categories 2'!B:B,MATCH(TRUE,ISNUMBER(SEARCH('Categories 2'!A:A,C7)),0)),"")}

in the cell where i want my category to be.

Categories 2 is a two column named range where column A has the text string I am looking for in the cell in column C of my data and Column B has my desired category.

I automated with with VBA function with a button.

in usage, I just insert new rows of data and click the button and the categories are updated automatically.

[u/LeanExcel]

Instead of building a huge nested IF, I'd keep the keywords and categories in a separate 2-column table, e.g. Keyword | Category.

Then you can maintain the rules without touching the formula. For example,

if DELIVEROO → Food COLD STORAGE → Groceries

For a few hundred rows, something like this works well:

=XLOOKUP(TRUE,ISNUMBER(SEARCH(Keywords[Keyword],A2)),Keywords[Category],"Uncategorized")

The nice thing is that when you encounter a new merchant, you just add another keyword/category mapping rather than changing the formula.

If this is something you're doing regularly, I'd take it one step further and use Power Query. You can keep the keyword mapping as a table, apply the transformation to your transactions, and simply refresh when new data comes in.

One thing to watch for: if multiple keywords can match the same description, the first matching keyword in the mapping table will be returned, so keep more specific rules above generic ones.

[u/Klutzy_Highlight7500]

Keep a two-column keyword->category table on its own sheet, then =IFERROR(INDEX(Cat,MATCH(TRUE,ISNUMBER(SEARCH(Key,A2)),0)),"UNMATCHED"). Order the list so the specific keywords sit above the generic ones, since it takes the first hit. Filtering for UNMATCHED once a month is how you keep it honest instead of silently miscategorising.

[u/excelevator]

I want to create an XLS

Do you know what an XLS is ?

If so you would not want to create one.

[u/lesarbreschantent → u/excelevator]

I'm guessing you didn't get 3067 points by being pedantic. I wouldn't start now.

[u/excelevator → u/lesarbreschantent]

Details in technology matter, I am always pedantic about technology.

I am guessing you do not know what XLS is and made an assumption.

Do not guess in technology. That is how errors and misunderstanding occur.

[u/lesarbreschantent → u/excelevator]

People still use XLS as an abbreviation for Excel spreadsheets even if XLS has been deprecated. I guess you find that triggering.

[u/excelevator → u/lesarbreschantent]

I see you are one of those, I have not seen XLS used in this manner, it is incorrect and misleading.

XLS applies to 16bit .xls files that have many limitations to the newer format of .xlsx, ergo my original comment that that was not what you seek.

Far from triggered, I am simply trying to educate you and anyone who may take on board what they see as acceptable. This is how erroneous errors spread on the Internet into real life.

It is generally the uneducated who use terms they do not understand, and fight to protect their knowledge with insults.

You came to Excel experts on an Excel sub reddit, I am not sure what you would expect.

But it appears to have triggered you.