Vlookup stands for ‘vertical’ lookup for Google Sheets and is a built-in Excel function. It searches the entire column in the data to look up a certain value and match it to the corresponding data value in another column. For example, the alphabets ‘tri’ would return and be a part of several words such as tricycle, acentric, airstrike, etc.
VLOOKUP with a single wildcard
If you have a table of students information and you have been given a partial name (example: “Rac”) to find and retrieve information about the student then you would use the default VLOOKUP formula but with the wildcard asterisk character *
What this does is that you will search through the First Name column to find the one starting with “Rac” even though you don’t know the full name of the student.
The following formula would be used to complete that, eg look up the last name:
It would search for the string “Rac*” where * is referred to as a wildcard and it could be a string of anything, or nothing, that would follow on after “Rac”. In other words, it would match “Rac”, “Rachel”, “Rachel123”, “Rachel@gmail”,…etc.
The main formula is just the standard VLOOKUP.
To look at the workbook to create a similar table to practice, click here.
VLOOKUP with two wildcards
What if your search phrase has missing characters before as well as after it? That’s simple too! You can simply add a second wildcard before the search phrase and proceed as explained in the previous example. If you searched for “ndl” The formula would then read:
As the wildcard characters would also represent nothing, then it won’t matter if there are no characters in front of your search phrase string.
Points to Note:
- If there are more than one matches on your VLOOKUP, the first one would be returned as the answer.
- We use FALSE as the last argument of your VLOOKUP in order for the wildcard matching to work.
We hope you found this article useful. Use our guide Free Keyboard Shortcuts: Spreadsheet Secrets and watch yourself transform from a Google Sheets newbie to a ninja in no time! Save your copy and add the shortcuts that you discover to quickly refer back anytime you need.
Stay subscribed to Wyzebulb Blog for more blogs and articles on Automation, Marketing, and Productivity!