The XLOOKUP match_mode guide
=XLOOKUP("*"&E2&"*", $B$2:$B$9, $A$2:$A$9, "No match", 2) finds the first name in B2:B9 that contains what's in E2, anywhere in it. The 2 at the end is match mode 2, which tells XLOOKUP to read * as a wildcard.
Three wildcard characters
| Character | Stands for | Example |
|---|---|---|
* | any run of characters, or none | "*Kettle*" finds The Copper Kettle |
? | exactly one character | "AB-??" finds AB-12 but not AB-1 |
~ | the next * or ? as itself | "Why~?" finds Why? but not Whys |
Match mode 2 turns them on
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
XLOOKUP's default is an exact match, and in an exact match * is just a star. Without match mode 2, "*Kettle*" looks for a customer literally called *Kettle*, finds none, and returns #N/A. Match mode is the 5th argument, so the 4th (what to show when nothing is found) comes first, even when you leave it empty: …, $A$2:$A$9, , 2).
Build the pattern from a cell
The stars are text, so they go in quotes, and & joins them to the cell: "*"&E2&"*". Writing "*E2*" puts the cell address inside the quotes, and XLOOKUP looks for the letters E2.
E2&"*": names that start with E2."*"&E2: names that end with it."*"&E2&"*": names that contain it anywhere.
What to know before you trust it
- It returns the first match. If two customers contain "Mill", you get the higher one in the list. Search mode
-1starts from the bottom instead. - Capitals don't matter. "kettle" finds Kettle.
- Wildcards work on text. A pattern like
"12*"doesn't find the number 123. - No match. Without the 4th argument you get #N/A. With it, you get your own text, such as "No match".
VLOOKUP with FALSE and MATCH with 0 read wildcards too, without a match mode. So do the conditions in SUMIF and COUNTIF.
Common mistakes
- Leaving out the 2. Every row says #N/A, or your "No match", even for names that are there.
- 2 in the 4th place.
XLOOKUP(…, $A$2:$A$9, 2)means "show 2 when nothing is found". - A star on one side only. Names where your text sits in the middle aren't found.
Exact matches first: XLOOKUP practice.
FAQ
- How do I use wildcards in XLOOKUP?
- Set match_mode, the 5th argument, to 2. For example =XLOOKUP("*"&E2&"*",B2:B9,A2:A9,"No match",2) finds the first name in B2:B9 that contains E2.
- Why is my XLOOKUP wildcard not working?
- Usually the match mode is missing. Without 2 as the 5th argument, XLOOKUP reads * and ? as plain characters.
- How do I look up a value that contains text in Excel?
- Put a * on both sides of the text: "*"&E2&"*". XLOOKUP with match mode 2, VLOOKUP with FALSE and MATCH with 0 all read it as "contains".
- How do I search for a real asterisk or question mark?
- Put a tilde in front of it: "Why~?" finds Why? and nothing else.
- Can XLOOKUP return every match?
- No, it returns the first one. FILTER, in Excel 365 and 2021 or later, returns all of them.



































































