Hi,
I’ve been following the great guidance setting up a finance budget tracker. I’m using the following lookup to categorise bank statement transactions:
=XLOOKUP(TRUE,ISNUMBER(SEARCH(CatgTbl[Keyword],[@Description])),CatgTbl[Sub-Category],"Uncategorised")
The problem I'm having is for items which 'split' the categories with similar keywords.
For example, ‘Tesco PFS’ (which stands for Petrol Filling Station) should be in the 'Car & Transport' category and 'Petrol' sub-category, but it seems to be pulling through 'Food and Drink' category and 'Groceries' sub-category. I assume this is because I have another keyword in the categories table just for 'Tesco' which I assume is causing the problem in relation to pulling through the first match it finds 'Tesco'.
I've tried to use " " to be more specific with the "Tesco PFS" in the Keyword table but it still returns an incorrect category.
Strangely ‘TESCO PAY AT PUMP’ does bring back the correct categorisation of Car & Transport > Petrol.
Here’s the Tesco categories I’ve set up:
|
Type |
Category |
Sub-Category |
Keyword |
|
Expense |
Car & Transport |
Car Insurance |
TESCO INSURANCE |
|
Expense |
Car & Transport |
Petrol |
TESCO PAY AT PUMP |
|
Expense |
Food & Drink |
Groceries |
Tesco Stores |
|
Expense |
Food & Drink |
Groceries |
Tesco |
|
Expense |
Car & Transport |
Petrol |
TESCO PFS |
I’m sure there is a simple explanation to this behaviour but not had any luck of yet. Would really appreciate any advice on how I can overcome this problem. Example of the incorrect pull through below:
17-Nov-25 TESCO PFS 3995, -55.54, Expense, Food & Drink, Groceries
23-Jan-26 TESCO PFS 3995 , -53.17,Expense, Food & Drink, Groceries
Thank you
To fix that issue, put the keyword Tesco last in the list of Tesco related categories. SEARCH is not case sensitive and will return an array of value errors and numbers.
The picture below illustrates what SEARCH actually returns in your example.
Then XLOOKUP finds the first number value, shaded orange (incorrect) and green (correct), and returns the category that belongs to the keyword in that place. When you move the "Tesco"-only keyword towards the end of the list with keywords that have "Tesco" in it, you'll always pick-up the category for the "longer Tesco keywords" when found.
But let's assume you'll have a new Tesco description next week. For example "TESCO PETROL". If you haven't added it to the category list above the "Tesco" keyword it will still return the Groceries category belonging to "Tesco". The "Uncategorized" warning from XLOOKUP will not show.
Something you need to live with I guess. Just remember that any Tesco description needs special attention.
Hello,
Try with following example:
=XLOOKUP(TRUE,(CatgTbl[Keyword]=[@Description]),CatgTbl[Sub-Category],"Uncategorised")
As long you keep the keywords unique it will give correct result.
Br,
Anders