One of the obvious problems with VLOOKUP is that it searches for an exact match i.e. the contents of the entire lookup cell must match the entire contents of the table. This can be a problem when you want it to find a partial vlookup match where the lookup value is only part of the cell contents e.g. if I am looking for the surname ‘Bath’ in a list of names, a traditional VLOOKUP would not associate ‘Anita Bath’ with ‘Bath’ as they are not a match.
This is where wildcard characters can be very useful.
The two wildcard characters are the ? and the *.
A ? tells Excel that you are looking for one character per ?, so for Anita Bath I could do a VLOOKUP for “??????Bath” and Excel will find it (notice the 6 ?’s representing the word Anita and the space after it).
A * tells Excel that there are some characters in that spot but we don’t know the exact number, so an alternate VLOOKUP for Anita Bath would be “*Bath”.
So in the example below, note that in the VLOOKUP we have included a * in front of and after the surname ‘Bath’ and put it inside quotes. This tells Excel to look for the word ‘Bath’ in column B and it might have other characters in front of or behind it.
If you are happy to type the surname into the formula you can use this technique. However, if you want the VLOOKUP to look at a cell, you need to insert the wildcard characters into the formula.
You can do this by using the CONCATENATE or & functions. These functions allow you to create a single item of text out of a number of cells.
Below notice that in the VLOOKUP lookup value we have used “*”&F7&“8” . The &’s tell Excel that we want to join a * with the contents of cell F7 and another *. Note that the result looks the same as if you typed it in “*Bath*” but now you can change the contents of F7 and get a new result.
For an example using the ?, see below.
For whatever reason we know that the Anna we are looking for has a surname of 8 characters and we need a space. Note that in the VLOOKUP we use F8&“?????????” as the lookup value and it correctly pulls the birthday of Anna Prentice and not Anna Fender.
In this way you are able to get VLOOKUP to identify partial matches and bring through the relevant information