📎 This article includes 1 downloadable practice file ↓
Real data rarely matches exactly. The bank says “RAJ TRADERS PVT LTD”, your ledger says “Raj Traders”, and someone searches for “raj”. Wildcards and the SEARCH function let lookups match part of a value — powerful, and easy to get wrong.
In this article
The two wildcards
| Wildcard | Means | Example |
|---|---|---|
* |
Any number of characters | "Raj*" starts with Raj; "*Ltd" ends with Ltd; "*paper*" contains paper |
? |
Exactly one character | "INV-??" INV- plus two characters |
~ |
Escape: a real * or ? | "A~*" matches the text A* |
VLOOKUP and XLOOKUP with wildcards
=VLOOKUP("*"&J1&"*", D2:I201, 6, FALSE) ' first customer containing J1
=XLOOKUP("*"&J1&"*", D2:D201, I2:I201, "Not found", 2) ' XLOOKUP needs match mode 2VLOOKUP’s exact mode accepts wildcards automatically; XLOOKUP only does when the fifth argument is 2. Both return the first match only.
Count and sum with “contains”
=COUNTIF(D2:D201, "*Traders*") ' how many customers contain "Traders"
=SUMIFS(I2:I201, D2:D201, "Raj*") ' total for names starting with Raj
=SUMIFS(I2:I201, E2:E201, "*"&J1&"*") ' contains the word typed in J1Wildcards in COUNTIF/SUMIFS are case-insensitive.
All matches, not just the first (Excel 365)
=FILTER(D2:I201, ISNUMBER(SEARCH(J1, D2:D201)), "No match")SEARCH returns a position when the text contains J1 and an error when it doesn’t; ISNUMBER turns that into TRUE/FALSE for FILTER. Use FIND instead of SEARCH for case-sensitive matching.
Match on the start of a code
=FILTER(A2:I201, LEFT(K2:K201, 2) = "27") ' GSTINs from Maharashtra (state code 27)LEFT/RIGHT comparisons are more precise than wildcards when you know the exact position.
Where people go wrong
- Wildcards don’t work on numbers —
"45*"won’t match the number 4521. Convert withTEXT()or compare with LEFT(A2,2)=”45″. - XLOOKUP returns #N/A with “*” — you forgot match mode 2.
- Too many matches — add more of the name, or match on a code instead of a name.
- Real asterisks in data (product “A*B”) — escape with
~*.
Practice
The combo practice workbook below has a Sales sheet of 200 orders and a task for every formula on this page. Type your formula in the yellow column; the check turns green when the answer matches. The Answers sheet has working versions.
More combinations: all formula combos · functions used here are explained in the Excel function course.
📎 Practice files for this article
- 📗Formula combinations practice workbook50 tasks: lookups, running totals, ranks, top-N, unique counts, FY, age, text extraction, list comparison, weighted averages, ageing, rolling totals, duplicates and working days — with automatic checks.⬇ XLSX · 39 KB
Free to use for learning. Files with macros (.bas) are plain text — import them with Alt+F11 → File → Import File, and always test on a copy.
Stuck on a step? Ask a question and the AI answers using this article.