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.
𝗪𝗶𝗹𝗱𝗰𝗮𝗿𝗱𝘀
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
𝗫𝗟𝗢𝗢𝗞𝗨𝗣 𝗠𝗮𝘁𝗰𝗵 𝗠𝗼𝗱𝗲𝘀
Page 2 shows the options the [𝚖𝚊𝚝𝚌𝚑_𝚖𝚘𝚍𝚎] argument offers. Let’s focus on the highlighted ones.
0 – 𝘌𝘹𝘢𝘤𝘵 𝘮𝘢𝘵𝘤𝘩 performs an exact match, meaning the 𝚕𝚘𝚘𝚔𝚞𝚙_𝚟𝚊𝚕𝚞𝚎 must be identical to the one found in the 𝚕𝚘𝚘𝚔𝚞𝚙_𝚊𝚛𝚛𝚊𝚢.
2 – 𝘞𝘪𝘭𝘥𝘤𝘢𝘳𝘥 𝘤𝘩𝘢𝘳𝘢𝘤𝘵𝘦𝘳 𝘮𝘢𝘵𝘤𝘩 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 – 𝘌𝘹𝘢𝘤𝘵 𝘮𝘢𝘵𝘤𝘩 if [𝚖𝚊𝚝𝚌𝚑_𝚖𝚘𝚍𝚎] is omitted.
𝗘𝘅𝗮𝗺𝗽𝗹𝗲𝘀
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.
