Being able to use wildcard characters like * and ? in searches is something we are used to having. Now XLOOKUP allows you to do those types of searches in Excel.
This is the fourth post in a series of posts on the new XLOOKUP function. This time we look at using wildcard characters in searches. The other posts are listed at the bottom of this post.
Even though it is called XLOOKUP. The default operation looks down. Its starts at the top and works it way down looking for the code in question.
In the image below column D is using wildcards.
A wildcard is a character that represents
- a single character – the question mark ?
- multiple characters – the asterisk (or star) * – multiple characters can also mean no characters
To make XLOOKUP apply the wildcard search you must select 2 in the 5th argument.
Let’s look at the result of the XLOOKUPs.
Cell D2 finds the first code that starts with ABC. This finds the first code ABC123.
Cell D3 finds the first code that starts with ABC3. This finds the last code ABC321.
Cell D4 finds the first code that is 6 digits long, starts with FED and ends with a 1. The ? represent a single character. It finds FED321.
Cell D5 is similar to D4 but is uses the * which can also represent no characters. It also finds FED321. Check out cell D5 in the Looking Up section below to see how this is slightly different to cell D4.
Cell D6 finds the first code that is 6 digits long, the second letter is B and ends in 321. It finds CBA321.
Cell D7 finds the first code that is 6 digits long, the second letter is B and ends in 123. It finds CBA123.
Cell D8 finds the first code that start with CBA. It finds CBA I cell A4 not CBA321 in cell A7. This demonstrates the * can represent no characters.
The above examples used the default XLOOKUP which, surprisingly, looks down. To make XLOOKUP actually look up you need to change the last argument to -1. Examples below. You can see the differences in results.
Cells D2 and D3 both find the last entry.
Cell D4 works the same as before.
Cell D5 works a little differently to cell D4 this time. I have changed the code in cell A6 to FED1. Using FED*1 will find this code. The * can represent no characters. So FED*1 means the code must start with FED and end with 1. How many total characters in the code is irrelevant. The ? is a character placeholder. So FED?1 would not find FED1.
D6 returns a different answer as it starts form the bottom and work its way up the list.
Cells D7 + D8 return the same answers as before.
The Tilde character and wildcards
The tilde is ~. It is the key below the Esc key and you need to use Shift.
What if your codes include a * or a ?. It unlikely but it happens.
In that case you must enclose the * or ? between tildes – see example below.
Cell D5 and D8 both find ABC***.
Without the tildes the top result all find ABC*.
Cell D7 finds ABC as it starts at the bottom – it has -1 as its last argument.
Previous XLOOKUP posts