Wildcards: The Travel Line Came Back 1,624.30 Against a 1,600.00 Budget, and Travel Was 1,570.30

The month closed at 2,014.69 across twelve card transactions, and one line of the summary got queried: Travel, 1,624.30, against a budget of 1,600.00. Twenty-four pounds thirty over. Not a scandal — the kind of number that gets a sentence of explanation and a note to watch it next month.

Travel that month was 1,570.30. Twenty-nine seventy under.

Two separate things went wrong and both were done by a single character. A rule reading DELTA* was written to catch the airline and caught a broadband company called Deltacom, moving 89.00 into Travel. A rule reading *CAB* was written to catch taxis and caught an order from Cabletech, moving 143.00 in after it. And 178.00 of Eurostar matched no rule at all, so it was never counted anywhere. Add 232.00 that did not travel, subtract 178.00 that did, and the summary is 54.00 high — just enough to cross the budget line and flip the sign of the variance.

What this covers. COUNTIF, COUNTIFS, SUMIF, SUMIFS, MATCH, VLOOKUP, SEARCH, FIND, EXACT, ISNUMBER, SUBSTITUTE and SUMPRODUCT work in every version this century, and everything essential here is built from them. XLOOKUP, FILTER, LET and spilled arrays need Microsoft 365 or Excel 2021; where one is used, the older equivalent is beside it. Wildcards themselves are identical in every version and in every language of Excel: *, ? and ~ are the same three characters whatever the interface says.

1) Twelve Lines and Five Rules

The statement is in A1:D13. The rules somebody wrote to categorise it are in F2:G6:

Five rules, in that order, first match wins. Here is what they actually do to the twelve rows:

Seven rows matched something. Five matched nothing, and those five are worth 366.24 — 18% of the card. Nobody noticed, because a row that matches no rule does not produce an error; it produces a blank, and a blank in a category column looks like a row somebody has not got to yet.

🎯 Scenario: Before you write a single rule, put =SUM(C2:C13) somewhere and write the answer down: 2,014.69. Every categorisation in this article is checked against that one number, and the check takes one cell. A category summary that does not add back to the statement total is not a summary, it is an opinion.

2) The Two Rows That Went to Travel

Take DELTA* literally, because Excel does: the text DELTA, followed by any number of any characters, and nothing else in the cell. "DELTACOM BROADBAND" is the text DELTA followed by COM BROADBAND. It matches, exactly as designed, and the design is the problem.

=SUMIF($B$2:$B$13,"DELTA*",$C$2:$C$13)     → 951.40
=SUMIF($B$2:$B$13,"DELTA AIR*",$C$2:$C$13) → 862.40

The 89.00 between those two numbers is the broadband bill. Nothing about the first formula is broken — it does what it says, and what it says is broader than what was meant.

*CAB* is the same mistake with both ends open: anything, then CAB, then anything. "GREEN CAB CO" qualifies. So does "CABLETECH SUPPLIES", and so would "CABINET REPAIRS", "SCABBARD LTD" and every other word in English with those three letters in it.

=SUMIF($B$2:$B$13,"*CAB*",$C$2:$C$13)   → 186.00
=SUMIF($B$2:$B$13,"* CAB *",$C$2:$C$13) → 43.00

The 143.00 between those two is the Cabletech order, and the only difference between the formulas is a space either side of CAB. "GREEN CAB CO" has a space before and after those three letters; "CABLETECH SUPPLIES" has neither. Section 4 is about why that stands in for a word boundary and where it stops working.

The habit worth taking from both pairs: you do not confirm a wildcard pattern by reading it, you confirm it by counting it. Put =COUNTIF($B$2:$B$13,F2) beside every rule and read the hit count before you trust the rule.

🎯 Scenario: For every rule you write, write its hit count beside it: =COUNTIF($B$2:$B$13,F2). On this data that column reads 3, 1, 2, 1, 1. The 3 against DELTA* is the whole story — two flights and something else — and it is visible before anyone has totalled a category.

3) Which Side of the Function the Pattern Sits On

This is the part that decides how a rules table can be built at all, and it is almost never written down.

In the *IF family the pattern goes in the criteria. The range holds plain values; the criteria holds the pattern:

=COUNTIF(B4,"DELTA*")   → 1
=SUMIF($B$2:$B$13,F2,$C$2:$C$13)

In the lookup family the pattern goes in the lookup value. The array holds plain values; the thing you are looking for is the pattern:

=VLOOKUP("DELTA*",$B$2:$C$13,2,FALSE)      → 612.40
=MATCH("*HOTEL*",$B$2:$B$13,0)             → 4
=XLOOKUP("*HOTEL*",$B$2:$B$13,$C$2:$C$13,,2) → 486.90

And in every plain comparison — =, IF, FILTER, SUMPRODUCT(--(B2:B13="DELTA*")) — there are no patterns at all. * is an asterisk:

=B4="DELTA*"                     → FALSE
=COUNTIF(B4,"DELTA*")            → 1
=SUMPRODUCT(--(B2:B13="DELTA*")) → 0

There is a third position to watch, which is the range. =SUMPRODUCT(--(COUNTIF($B$2:$B$13,"DELTA*")>0)) returns 1, not 3 and not a column of TRUEs: COUNTIF over a multi-cell range returns a single number — here 3 — so the comparison is one TRUE. To get one answer per row you either point COUNTIF at one cell and fill down, or use ISNUMBER(SEARCH(...)), which is built to take a range on that side.

The consequence for a rules table is direct, and it catches people who reach for the newest function first. A rules table stores patterns in a column and tests each one against a description. XLOOKUP with match mode 2 reads its wildcards in the lookup value, so =XLOOKUP(B2,$F$2:$F$6,$G$2:$G$6,"Uncategorised",2) asks Excel to treat the description as the pattern and the rules as literals — the exact opposite of the design — and it returns "Uncategorised" for all twelve rows. Nothing errors. The column just fills with a plausible word.

COUNTIF is the function that will take an array of patterns, because its criteria argument accepts one:

=COUNTIF(B4,$F$2:$F$6)   → {1;0;0;0;1}

One cell tested against five patterns, and the answer says row 4 matches rule 1 and rule 5. That array is the engine of section 8.

🎯 Scenario: When a lookup that should match returns the not-found value for every row, check the side the pattern is on before you check anything else. Wildcards on the wrong side of a function do not error — they simply never match, which reads exactly like a missing rules table.

4) Whole Cell, or Anywhere in the Cell

COUNTIF and its family compare against the whole cell. SEARCH and FIND look for a substring anywhere. That single difference explains why a rules table needs asterisks and a SEARCH test does not:

=COUNTIF($B$2:$B$13,"CAB")        → 0
=COUNTIF($B$2:$B$13,"*CAB*")      → 2
=SUMPRODUCT(--ISNUMBER(SEARCH("CAB",$B$2:$B$13))) → 2

The middle and bottom formulas are the same test written twice. The top one is the mistake everybody makes once: a criteria of CAB asks for a cell whose entire contents are the three letters C, A, B, and no bank statement has ever contained one.

Excel has no word boundary, no \b, no "whole word" option in formulas. The substitute is to pad both the value and the pattern with spaces, so a space can stand in for a boundary:

=COUNTIF($B$2:$B$13,"* CAB *")                  → 1
=SUMPRODUCT(--ISNUMBER(SEARCH(" CAB "," "&$B$2:$B$13&" "))) → 1

One row: "GREEN CAB CO". "CABLETECH SUPPLIES" has no space before its CAB, so it is gone — 143.00 out of Travel for the cost of two spaces. The padding on the second formula matters: without it, a description that begins with the word would fail, because there is no space in front of it.

🎯 Scenario: Keep the two shapes straight in your head by what they need: a criteria for the *IF family describes the whole cell, so it usually needs asterisks; a find text for SEARCH describes a fragment, so it usually needs none. Asterisks inside a SEARCH are legal and are almost always somebody applying the wrong habit.

5) The Five Rows That Matched Nothing

Rows 3, 9, 10, 11 and 12 matched no rule, and they are worth 366.24. Four of them are merchants whose descriptions the rule writer had never seen; one of them, the 178.00 Eurostar, is the travel that should have been in the very total being argued about.

The formula that finds them is the count of matching rules per row:

=SUMPRODUCT(COUNTIF(B2,$F$2:$F$6))

Filled down, that column reads 1, 0, 2, 1, 1, 1, 1, 0, 0, 0, 0, 1. Two things are visible at once: five zeros, and one 2. And the total of that column is 8 against 7 rows that matched anything at all:

=SUMPRODUCT(COUNTIF($B$2:$B$13,$F$2:$F$6))         → 8
=SUMPRODUCT(--(COUNTIF($B$2:$B$13,$F$2:$F$6)>0))   — counts rules that hit, not rows

Eight hits over seven rows means exactly one row is claimed twice, and the per-row column says which: row 4, the broadband bill, matched DELTA* and *BROADBAND*.

The money that matched nothing comes out of the same idea:

=IF(SUMPRODUCT(COUNTIF(B2,$F$2:$F$6))=0,C2,0)          — one row
=SUM($C$2:$C$13)-SUMIF($H$2:$H$13,"<>Uncategorised",$C$2:$C$13)  → 366.24

where H is the category column from section 8. Or, with a helper column of hit counts in I: =SUMIF($I$2:$I$13,0,$C$2:$C$13) → 366.24.

🎯 Scenario: Never let a categoriser return a blank. Return the word "Uncategorised" and put =SUMIF(H2:H13,"Uncategorised",C2:C13) in the summary next to the categories. A blank is invisible in a pivot table and a named bucket with 366.24 in it is not.

6) The Asterisks That Are Really There

Five of the twelve descriptions contain a literal asterisk, because that is how the payment processors write themselves: SQ * is Square, TST* is Toast, PAYPAL * is PayPal, and Amazon Marketplace puts one in the middle of an order reference. This is not exotic data. It is what a card feed looks like.

Now use one of those descriptions as criteria, which is what any duplicate check or per-merchant subtotal does:

=COUNTIF($B$2:$B$13,B3)             → 2
=SUMIF($B$2:$B$13,B3,$C$2:$C$13)    → 42.75

B3 is "SQ *BLUE BOTTLE", which appears once. COUNTIF reads it as the text SQ , then anything, so it matches B3 and B7 — and the per-merchant total comes back 42.75 instead of 18.60, over-stating that merchant by 24.15. On a real feed with two hundred Square rows, that is a merchant analysis nobody can reconcile.

The escape character is the tilde. ~* means a literal asterisk, ~? a literal question mark, ~~ a literal tilde:

=COUNTIF($B$2:$B$13,"SQ ~*BLUE BOTTLE")  → 1

Typing that by hand for every merchant is not a plan. To escape a value coming from a cell, substitute all three characters — tilde first, or the tildes you add for the asterisks get escaped in turn:

=COUNTIF($B$2:$B$13,SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B3,"~","~~"),"*","~*"),"?","~?"))  → 1

The alternative is to stop using pattern functions for exact comparisons altogether:

=SUMPRODUCT(--EXACT($B$2:$B$13,B3))           → 1
=SUMPRODUCT(--EXACT($B$2:$B$13,B3),$C$2:$C$13) → 18.60

EXACT reads no wildcards and no case-folding. It is slower and it is right, and on a column of merchant strings that is the trade you want.

🎯 Scenario: Any time a cell value is used as criteria — duplicate flags, per-key subtotals, reconciliation counts — ask whether that column can contain *, ? or ~. Card feeds, product codes, file paths and formula text all can. =SUMPRODUCT(--ISNUMBER(FIND("*",B2:B13))) answers it in one cell: on this sheet, 5.

7) SEARCH, FIND and EXACT

Three functions that look interchangeable and are not:

SEARCH honouring wildcards is the one that surprises people, and it shows up the moment you go looking for a literal asterisk:

=SUMPRODUCT(--ISNUMBER(SEARCH("*",$B$2:$B$13)))  → 12
=SUMPRODUCT(--ISNUMBER(FIND("*",$B$2:$B$13)))    → 5

Twelve versus five. SEARCH("*",...) asks "does this cell contain any sequence of characters, including none?", which every cell does. FIND is the only one of the pair that reads an asterisk as an asterisk, and five is the true answer.

The same pair decides case questions. COUNTIF, SUMIF and SEARCH are all case-insensitive, so delta* and DELTA* are one rule; if a category genuinely depends on case — some ledger codes do — FIND and EXACT are the only tools that will see it.

🎯 Scenario: Default to ISNUMBER(SEARCH(...)) for "does this contain that", because case-insensitive is what you want on merchant names. Switch to FIND for exactly two reasons: the fragment you are looking for contains * or ?, or case is part of the meaning.

8) One Category Per Row: First Match Wins

The rules table needs to produce one answer per row, in rule order, with a named fallback. COUNTIF against the array of patterns gives the match vector; MATCH takes the first 1 out of it:

=IFERROR(INDEX($G$2:$G$6,MATCH(1,COUNTIF(B2,$F$2:$F$6),0)),"Uncategorised")

In Microsoft 365 that is an ordinary formula. In Excel 2019 and earlier, confirm it with Ctrl+Shift+Enter. Filled down H2:H13 it produces:

It adds back to the statement exactly, which is the one virtue this version has: every row goes to exactly one place. Telecoms is 0.00 because its only transaction was taken by DELTA* three rules earlier — first match wins, and the rule order is the whole ruleset.

Now compare the summary somebody would more likely build, one SUMIF per rule:

=SUMIF($B$2:$B$13,F2,$C$2:$C$13)

Travel is 1,624.30 in both. But this one totals 1,737.45 over rows worth 1,648.45, because the 89.00 is counted in Travel and in Telecoms. The categories add to more than the transactions they cover, and the difference is precisely the ambiguity from section 5.

Two different constructions, two different wrong answers, and only one of them can even be reconciled. The version with a category column can be checked against 2,014.69; the version with one SUMIF per rule cannot be checked against anything at all.

🎯 Scenario: Build the category column first and the summary from it, never one SUMIF per rule straight into the report. =SUMIF($H$2:$H$13,"Travel",$C$2:$C$13) reads from a column you can eyeball row by row, and =SUM(C2:C13)-SUM(summary) proves nothing is double-counted or dropped.

9) Tightening the Rules Until the Sheet Reconciles

The fix is not a cleverer formula. It is ten better rules, ordered most specific first, and the tilde used where the data has real asterisks in it:

Re-run the category column and the summary reconciles:

Travel is 1,570.30 against a 1,600.00 budget: 29.70 under, where the first version said 24.30 over. Nothing about the card changed. Two rules got narrower, one got written, and three learned to read an asterisk as an asterisk.

Note what the ordering does. *BROADBAND* above DELTA AIR* is belt and braces — the narrower airline rule already excludes Deltacom — but rule order is the only tie-breaker a first-match-wins table has, and putting the specific above the general is the habit that survives the next rule somebody adds in a hurry.

🎯 Scenario: Write rules from the specific to the general, and re-check the hit counts after every insertion. A rule added at the top of the list can silently take rows from three rules below it, and the only symptom is a category total that moved.

10) Three Checks That Keep It Honest

One: the column of hit counts. =SUMPRODUCT(COUNTIF(B2,$F$2:$F$11)) down the rows. A 0 is an uncategorised transaction, a 2 or more is an ambiguity that rule order is currently deciding for you. On the original five rules: five zeros and one 2.

Two: the reconciliation. =SUM(C2:C13)-SUMIF($H$2:$H$13,"<>",$C$2:$C$13) must be 0.00, and the summary of categories must add to 2,014.69. If the summary is built from one SUMIF per rule, this check cannot pass and cannot be made to pass.

Three: the rules that never fire. =COUNTIF($B$2:$B$13,F2)=0 beside each rule. A rule with no hits is either dead weight from an old feed or a rule whose pattern is wrong — CAB instead of *CAB* is the classic, and it reports zero rather than complaining.

Two more worth adding when the feed is large: =SUMPRODUCT(--ISNUMBER(FIND("*",$B$2:$B$13))) to know how much of the data contains literal asterisks before you use any of it as criteria, and =SUMPRODUCT(--(LEN($F$2:$F$11)>255)) to catch a rule that has grown past the criteria limit.

🎯 Scenario: Put all three checks in a three-cell block at the top of the rules sheet, not in a hidden tab. A rules table is code, and these are its tests; the day someone adds *AIR* to catch a second airline, the hit-count column is what tells them it also took two rows off DELTA AIR*.

11) Twelve Traps

1. =B4="DELTA*" is FALSE and =COUNTIF(B4,"DELTA*") is 1. The same string is a pattern in one place and text in another, and nothing in the formula tells you which.
2. *IF criteria match the whole cell. "CAB" finds nothing on any real description; "*CAB*" finds two. Nearly every "why does my COUNTIF return 0" is this.
3. Wildcards do not apply to numbers. =COUNTIF($C$2:$C$13,"6*") is 0, because the amounts are numeric. The same digits stored as text would match, so the answer changes with the cell format.
4. =COUNTIF(range,"*") counts text cells only — not numbers, not dates, not blanks. On B2:B13 it is 12; on C2:C13 it is 0. "<>*" counts the non-text cells, which is 12 on column C.
5. Wildcards are ignored inside comparison criteria. ">DELTA*" compares text alphabetically against the literal string; it does not mean "after anything starting with DELTA".
6. VLOOKUP honours wildcards only with exact match. VLOOKUP("DELTA*",...,FALSE) works; the same lookup with TRUE treats the pattern as text and returns whatever the sorted approximate match lands on.
7. XLOOKUP honours wildcards only in match mode 2, and only in the lookup value. Its default mode reads * as an asterisk, so a pattern silently becomes a not-found.
8. SEARCH honours wildcards; FIND does not. SEARCH("*",A1) is 1 for every cell. To locate a literal * or ?, FIND is the only choice.
9. The tilde escapes, and must itself be escaped first. SUBSTITUTE the ~ before the * and ?, or the escapes you just inserted get escaped in turn.
10. Find & Replace reads wildcards too. Replacing * with nothing empties every cell it matches rather than removing asterisks; ~* is what you meant, and Ctrl+Z is what you will need if you forget.
11. Text criteria over 255 characters return #VALUE!. A rule assembled by concatenation can cross that line without anyone editing it.
12. AutoFilter and Advanced Filter have their own defaults. AutoFilter's "Contains" writes *text* for you; Advanced Filter treats a bare criterion as begins with, so typing Delta into a criteria range reproduces this article's original bug in a dialog box instead of a formula.

Practice

Using the twelve rows in the grid above:

1. Count before you total. Put the five original rules in F2:F6 and =COUNTIF($B$2:$B$13,F2) beside each. Which rule's hit count is wrong for what it was written to catch, and how would you know without reading the descriptions?
2. Both travel numbers. Write the single formula that returns 1,624.30 and the single formula that returns 1,570.30, and say which row moves between them and why.
3. Find the ambiguity. In I2:I13, count how many rules each row matches. Which row returns 2, which five return 0, and what is the total of the column?
4. The asterisk that is real. Explain why =COUNTIF($B$2:$B$13,B3) returns 2, then write two different formulas that return 1 — one escaping the value, one not using wildcards at all.
5. A word boundary. Write the criteria that catches "GREEN CAB CO" and not "CABLETECH SUPPLIES", then write the SEARCH version of the same test and say what the padding is for.
6. Reconcile. Build the category column with the tightened rules from section 9 and prove the summary adds to 2,014.69. Then add *AIR* → Travel at the top of the rules and say, without running it, what happens to Travel and why.

Summary

A wildcard is not a convenience, it is a second language sitting inside your formulas, and Excel never tells you which language a given argument is being read in. DELTA* in a COUNTIF criteria is a pattern; the same six characters in =B4="DELTA*" are text; and the asterisks the bank put in "SQ *BLUE BOTTLE" are a pattern the moment that cell is used as criteria and text everywhere else.

So: keep the pattern on the side of the function that reads it — criteria for the *IF family, lookup value for the lookup family, nowhere at all for a plain comparison. Write patterns that describe the whole cell for COUNTIF and fragments for SEARCH, and pad with spaces when you need the word boundary Excel does not have. Escape with ~ whenever a value from the data becomes criteria, or use EXACT and stop reading patterns entirely. And count the hits: a rule with a hit count beside it announces DELTA* catching three rows when it should catch two, and a rule with no hit count announces nothing until the month-end meeting.

The alternative is the version this article started with: 1,624.30 reported, 1,570.30 spent, a broadband bill filed under Travel, and a budget conversation about 24.30 that was never really there.