Spreadsheets

XLOOKUP with wildcards for partial merchant names

XLOOKUP wildcards for partial merchant names: how match_mode 2 and the asterisk pattern work, and why the lookup direction is reversed for keywords.

Editorial illustration for XLOOKUP with wildcards for partial merchant names

XLOOKUP matches partial text when the fourth-argument slot match_mode is 2 and the lookup value carries wildcards: =XLOOKUP("*"&$H$1&"*",$B$2:$B$500,$D$2:$D$500,"Not found",2). The asterisk stands for any characters. Wildcards work only in the lookup value, which is why the table of keywords cannot simply be used the other way round. XLOOKUP needs Excel 365 or 2021.

What do the arguments do?

  1. Lookup value: "*"&$H$1&"*". If H1 holds swiggy, this becomes *swiggy*, meaning any text with swiggy anywhere in it.
  2. Lookup array: $B$2:$B$500, the narration column being searched.
  3. Return array: $D$2:$D$500, for example the amount on the matching row.
  4. If not found: "Not found".
  5. Match mode: 2, which switches wildcards on. 0 is exact, -1 is exact or next smaller, 1 is exact or next larger.

So you type a word in H1, and XLOOKUP returns the amount on the first narration that contains it. A question mark stands for exactly one character, and a tilde in front of a star or question mark searches for the symbol itself.

Position matters too. A pattern like swiggy* with the asterisk only at the end means text that begins with swiggy, and *swiggy means text that ends with it. Using asterisks on both sides finds the word anywhere, which is usually what you want for narrations, but it also finds more false hits. Start with both asterisks and tighten the pattern if you see surprises.

Why does it not work in reverse for categorising?

The natural plan is a keyword table on a Rules sheet, with patterns like *swiggy* in column A, and a narration as the lookup value. It fails because Excel never reads wildcards in the lookup array, only in the lookup value. The narration UPI-SWIGGY-12345@ybl is compared to the literal text *swiggy*, which it does not equal. The table is inverted compared with what you need: wildcards belong around the small word, and the long narration belongs in the array being searched.

SetupResult
Lookup value *swiggy*, array of narrationsWorks: finds the first narration containing swiggy
Lookup value a narration, array of patterns like *swiggy*Fails: patterns in the array are treated as plain text
Lookup value "swiggy" with no asterisks, match mode 2Only matches a cell that is exactly swiggy

For narration-to-category work, use a SEARCH-based formula, which tests each keyword against the narration.

What is partial matching good for?

  • Finding one transaction: type part of a merchant name and see its amount or date.
  • Looking up a short code in a full-name list, such as typing metro to find Metro card recharge in a merchant master list.
  • Spot checks while you decide what rule to write.

How do you total every match, not only the first?

XLOOKUP returns one answer, the first match. To add up all rows that contain the word, use SUMIF, which understands wildcards in its criteria: =SUMIF($B$2:$B$500,"*"&$H$1&"*",$D$2:$D$500). For a list of every matching amount in Excel 365, use =FILTER($D$2:$D$500,ISNUMBER(SEARCH($H$1,$B$2:$B$500))).

Say three narrations containing swiggy have amounts of ₹420, ₹380 and ₹610. XLOOKUP gives ₹420 only, being the first. SUMIF gives 420 plus 380 plus 610, which is ₹1,410. Those amounts are invented.

Quick checklist

  • Match mode 2 is typed, not left out.
  • Asterisks sit around the keyword in the lookup value.
  • Narrations are the array; keywords are the lookup.
  • Test with one keyword you know appears and one you know does not.

When the formula returns Not found for a word you can see on the statement, look for double spaces or a hyphen where you expected a space in the narration, then search for a shorter fragment of the word.

If this reverse-lookup puzzle is a recurring headache, SpendixAI avoids it by categorising imported rows with your rules and history, so the sheet need only handle the reports you build.

SPENDIXAI JOURNAL

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

Explore your reports