To continue our Data Challenge series, this tutorial will focus on more advanced use cases of Excel wildcard. The goal of our data challenge series is to motivate you, our users and readers, to imbibe an attitude of continuous learning and to solve some common problems encountered in the field of data analytics and data science.
In this challenge, you’ll be tasked with retrieving customers’ balances using a lookup function in conjuction with a wildcard character in Excel.
The problem statement
For this data challenge, you are presented with two datasets:
- Customers list containing customers’ names and account balances in dollars ($)
- A search list with a subset of customers for which you need to retrieve their balances
This task will require you to use a type of lookup function e.g. XLOOKUP/VLOOKUP in tandem with a wildcard character in Excel, however, you can solve this using any combination of functions as long it’s not manually done.
Download the dataset
After downloading the dataset above, first, try your hands at the task before proceeding to view the step-by-step solution below.
*TIP: use a wildcard character with multiple criteria – first name and last name.
Be sure that you’re conversant with the dataset and that you’ve tried to solve the task before proceeding to the solution below.
The solution to the data challenge
Please note that this isn’t the only way possible to solve this task. The trick for us is to use a wildcard character with multiple criteria within a VLOOKUP/XLOOKUP function.
We will use the VLOOKUP function because not everyone has access to the XLOOKUP function (if you’re not using the Excel version on Office 365).
Step 1 – Use wildcard to retrieve customer balance
Open the Excel workbook and in the Customers List worksheet, highlight the data and turn it into a table by pressing Ctrl + T. Alternatively, after highlighting, go to the Insert tab and choose Table.
Give your table a name e.g. customers
Next, go to the Search List worksheet, within the balance column, enter the formula below:
Here, we use a wildcard within VLOOKUP to say – search for any name that contains that particular surname and firstname in that order (left to right).
This syntax “*“&B2&”*”&”*“&C2&”*” is the usage of wildcards with multiple criteria. Our lookup table is the customers table and our return array is column 2 – Customer Balance ($).
We are essentially concatenating the asterisk – * to the names like so – *B2*&*C2*.
This particular formula retrieves 54 customer balances from the list of 70, however, we can tweak it a little bit to improve the accuracy. The wildcards search happens in a particular order – left to right or right to left – so we can write another formula to combine both directions.
Step 2 – Reorder wildcard search
Here, we will combine two wildcard formula into an IFERROR function to account for the reverse direction search – right to left.
We simply introduce another IFERROR function to run another VLOOKUP in the reverse direction. So we’re saying that, if the left to right format returns an error, run the wildcard search from right to the left.
The performance increases from 54 retrieved values to 59 out of 70 which is an 84% success rate.
See below a snapshot of the results from both methods.
You can see that combining both directions in the wildcard search results in more retrieved balances. Try confirming this for yourself.
Great! That’s it really. I hope that you found this very useful to your analytics processes.
Catch you at the next Data Analytics Challenge. Until then, happy data sleuthing!
Check out this 3-hour crash course on Udemy at a great discount available for a limited period!
Check out our new How-To series page for video tutorials in DAX, Power BI visualizations, Power Query and Excel.
To find out more about what we do, subscribe to our insights below or you can reach out to us directly.
Related article – Visualizing WhatsApp Data using Python and Power BI