Wildcards in XLOOKUP

XLOOKUP is most commonly used for exact matches, but what if you don’t fully know what you’re searching for?

Fortunately, it supports wildcards, which are characters that take the place of text so you can perform partial matches. This is useful for widening the scope of your lookups.

Wildcards

The wildcards are defined on page 1. They are:

*
An asterisk represents any number of characters

?
A question mark represents one character

~
A tilde is the precursor for a literal character, in cases where *, ?, or ~ are looked up

XLOOKUP Match Modes

Page 2 shows the options the [𝚖𝚊𝚝𝚌𝚑_𝚖𝚘𝚍𝚎] argument offers. Let’s focus on the highlighted ones.

0 – Exact match performs an exact match, meaning the lookup_value must be identical to the one found in the lookup_array.

2 – Wildcard character match is used for partial matches, and gives special meaning to the *, ? and ~ characters.

For both, an #N/A error is returned if a match cannot be found.

XLOOKUP defaults to 0 – Exact match if [match_mode] is omitted.

Examples

Page 3 contains a table called tblData, which houses several text values alongside corresponding numbers. Below it are a few lookup examples for each wildcard that return matching numbers.

Notice how the exact matches respond differently to the wildcard ones. The former treats them just like any other character, whereas the latter doesn’t.