Spreadsheets

XLOOKUP wildcard picks the wrong merchant: how to fix the match

Why a short keyword like amazon also matches Amazon Pay Bill in your XLOOKUP merchant list, and how to fix it with specific keywords and a match counter.

Editorial illustration for XLOOKUP wildcard picks the wrong merchant: how to fix the match

A keyword lookup picks the wrong merchant because it returns the first row that matches, and a short keyword such as "amazon" matches everything that contains it: Amazon Pay Bill, Amazon Prime, Amazon Fresh. The fix has three parts: make the keywords more specific, put the most specific ones above the general ones, and add a check column that counts how many keywords hit each row.

How does the lookup find a category in the first place?

Say your keywords are in A2:A30 and their categories in B2:B30, and the bank narration is in F2. A common formula for "which keyword appears in this narration" is:

  • =XLOOKUP(TRUE,ISNUMBER(SEARCH($A$2:$A$30,F2)),$B$2:$B$30,"Uncategorised")

SEARCH looks for each keyword inside the narration and returns a position or an error. ISNUMBER turns that into TRUE or FALSE for every keyword. XLOOKUP then looks for the first TRUE going down the list and returns the category beside it. This needs Excel 365 or 2021. Another style puts a wildcard in the lookup value, such as XLOOKUP("*"&F2&"*",...,2), which matches the other way round: your text is inside the list entry. Both styles return the first hit.

Why does "amazon" catch the wrong row?

Take this keyword list, where the order is the order of the rows:

RowKeywordCategory
1amazonShopping
2amazon payBills
3amazon primeSubscriptions

The narration "AMAZON PRIME MEMBERSHIP" contains all of row 1 and row 3. Row 1 comes first, so the answer is Shopping, never Subscriptions. Rows 2 and 3 are unreachable. The formula is working as designed; the list is arranged badly.

How do I fix the keyword list?

  1. Sort from most specific to least specific. Put "amazon prime" and "amazon pay" above "amazon", so the general word is the last resort.
  2. Make keywords longer where you can: "amazon prime" instead of "prime", which would also hit unrelated narrations.
  3. Add a helper column with =LEN(A2) and sort the keyword table by it, largest first. Longer keywords are usually more specific.
  4. Remove keywords that are too short to be safe, such as "pay", "mart" or "uber", unless you want everything with that text to share a category.
  5. Rerun the lookup and check a few known rows by eye.

With the new order, the same narration now meets "amazon prime" before "amazon" and returns Subscriptions.

How do I catch problems before they spread?

Add a check column next to the result. In H2, count how many keywords matched:

  • =SUMPRODUCT(--ISNUMBER(SEARCH($A$2:$A$30,F2)))

A 0 means nothing matched and the row is Uncategorised. A 1 is unambiguous. A 2 or more means several keywords fit, and the first one won. Filter H for values above 1 and read those rows. They are exactly where a wrong answer can hide. Overlaps that return the same category, like "swiggy" and "swiggy instamart" both mapping to Food, are harmless; overlaps with different categories deserve a look.

What else causes a wrong match?

  • Case or spaces: SEARCH ignores capitals but not extra spaces. A keyword saved with a trailing space may never match. Wrap with TRIM when you build the list.
  • A keyword that is part of another word: "tea" matches "Steam Games". Add a space or use a longer name.
  • Merchants whose name changes by city or outlet; add the common variants as separate keywords.
  • The ranges are different sizes: $A$2:$A$30 against $B$2:$B$31 gives an error.

When should I stop patching?

A keyword list is a fair way to learn how categorising works, but every new merchant means another edit. SpendixAI uses your own rules first, then your history and similar merchants, and lets you add merchant aliases, so one correction carries to later statements.

SPENDIXAI JOURNAL

Useful ideas start with a clearer picture of your own spending.

Explore your reports