Wildcards are helpful in doing partial text match with VLOOKUP. Let's learn more about them and how they can be used.
.jpg)
Wildcards are the characters in Excel that are used with different functions to find the entire string (strings are the series of characters for ex: Anandagouda is a string that represents a name) with having only a partial part of that string (for ex: ‘Anand’ is a partial part of the string ‘Anandagouda’).
Asterisk (*), Question Mark (?) and Tilde (~) are the commonly used Wildcards in Excel.
Wildcards and VLOOKUP
Let’s say, in the following table, we wanted to find the marks of ‘Hariprakash Sharma’. We can easily do it using the VLOOKUP formula as the name ‘Hariprakash Sharma’ is present in the table along with his marks.
But there will be cases when you don’t have the entire name of ‘Hariprakash Sharma’ or any student for that matter, that’s where Wildcards will help you.
If you have partial text (for ex: ‘Hari’ in the above example) and you want to apply VLOOKUP for that partial text and get the result, you need to use Wildcards. Let’s discuss all three Wildcard Characters in detail.
Asterisk
Asterisk (*) - is used when you don't know the number of characters to find, after the VLOOKUP value.
We will find the marks of the student whose name starts with 'Hari'.
To use asterisk we will make the input value (Hari*)using '&' (& is used to combine two strings).
Now we will use “Hari*” as a input value in VLOOKUP and select the table (data), 2 (as column index number), False(argument to get the exact match)
=VLOOKUP(F4&"*",$B$5:$C$12,2,FALSE)
You can see the result 83 in the following screenshot. Also, note that we tried to use simple VLOOKUP in the cell F6 but VLOOKUP was not able to find the name 'Hari' as it didn't consider 'Hari' as a part of the string but the entire string.
You can also use Asterisk to find text that ends with the input value or the text that contains the input value somewhere in the middle. These scenarios are explored in the video embedded at the end of this blog.
Question Mark
Question Mark(?) - is used when you know the exact number of characters to find, after the lookup value.
It is used to find a fixed number of characters - if you want to find 3 characters then you are supposed to use 3 question marks "???" and the number of question marks will increase or decrease accordingly.
We'll use it with an example to know more about it.
Let's say in the above table, you have to find out the sales amount of a particular product ID by using partial invoice number - 1580
Now here, the number of characters after the lookup value are 3. So the value we'll add is "-???" (With (&)string). We are adding hyphen because there is a hyphen in the Product IDs.
To find out the sales amount : F4&-??? is the lookup value, select the table(data), 2 (as column index number) and 'False'(argument to get exact match)
=Vlookup (F4&"-???",$B$5:$C$17,2,FALSE)
₹1,866 is the corresponding sales amount of 1580(partial invoice number).
Note that we tried to use simple VLOOKUP in the cell F6 but VLOOKUP was not able to find the sales amount because it could only find the Product ID which only has '1580'.
Tilde
Tilde (~)- is used when either or both two characters (*)&(?) are present in the search string so that Excel doesn't get confused about whether it is supposed to use those two characters as wildcards or as characters.
In the above table, you can see the asterisk(*) and question mark (?) are present in the name.
We will find out the marks of 'Raj*' using(~). If you don't want to get wrong results, Use a tilde before an asterisk and question mark to nullify its effect, you can add it manually in the lookup value or by using the formula"=Substitute", this is the formula used to edit (substitute).
After adding(~) to the lookup value, select the table, 2(as column index number), False or 0 (argument for the exact match)
=Vlookup(F7,&"*~",$B$5:$C$10,2,0)
You can see in the cell F5 when we used simple VLOOKUP, how it confused the name with 'Rajesh*' and gave the result 32.
These are the wildcards and learning about them will help you do partial text match and have an edge in your career.
If you want to learn in-depth about wildcards, the following video is for you.
Join over 50,000+ brands already growing with Winuall.
