vlookup.co
Learn VLOOKUP HLOOKUP and INDEX MATCH In-depth

Wildcards in VLOOKUP

01 Types of Wildcards

Wildcards are used with Vlookup function for partial matches. We can use these wildcard characters Asterisk (*), Question Mark (?) and Tilde (~).

01 Asterisk (*)

This can be used to search for any number of characters. For example, if you are searching the word World with an asterisk you can use "*old” or “*ld” as the first parameter in Vlookup. You will still get the result if the word World is there. Example: Open the file Chapter 2.1 - VlookupAsterisk.xlsx Here we have names in A column and bonus amounts in B column. Now if enter the first name in E3 cell we should get the bonus amount in cell E4. So basically you want to search the person with first name Sam or John or Haris and the surname can be any name. [example] Enter the formula =VLOOKUP(E3&"*" ,A2:B11 , 2,0) in E4 cell. Now in E3 cell, we type the first name of the person followed by the concatenation operator (&) and then the wildcard character *. So you will get Sam* for first name and John* for second and Haris* for the third in the first parameter of the Vlookup formula. Whatever the surname it will be ignored and will search for the first name only. An asterisk will ignore all the characters after the first name and that text can be anything. So if the name is Sam Johnson or Sam David or Sam Alexander, it will always search for Sam.

01 Question mark (?)

This can be used to replace a single character. For example, you can search “Com?uter” for Computer. Or Co??uter for Computer replacing two characters. The question mark is used for replacing a single character or multiple characters. If you put three question marks, it will ignore three letters. Example: Open the file Chapter 2.2 - Vlookup Question Mark.xlsx Here we have product detail in the excel sheet beginning with Product id, Products, Qnty and Rate/Unit. [example] Each product id starts with WH or AH or GH at the beginning. But we want to ignore that and search only with numbers of the id's and fetch the products against each id. Enter the formula =VLOOKUP("??"&G5,A2:39,2,0) in G6 cell. So we have used the first parameter with two question marks followed by concatenation operator (&) and the cell reference where we type the product id. This will skip the first two characters WH or AH or GH and then consider the rest for searching. If you want to skip three characters you have to use three question marks. Example: Open the file Chapter 2.3 - Vlookup Question Mark.xlsx Here we have used the question mark in the middle of the search. We have the product id in A column followed by Products, Qnty and Rate/Unit. Product id has different three letters in the middle with leading and trailing hyphens and a 1000. [example] Here we want to search with first four letters of the product id to get the product name. So enter the formula =VLOOKUP(G5&"-???-1000",A2:39,2,0) in G6 cell. We have used G5&”-???-1000" as the first parameter in the Vlookup. G5 is the cell we enter the product id. So first we enter the cell reference G5 followed by concatenation operator (&). Then we typed the hyphen and three question marks to replace the different three letters. Rest of the characters are same, so we have used it as it is. Now we can search the product id with the first five characters and you will get the Products.

01 Tilde (~)

This is used to nullify the effect of the star (*) and question (?) mark. For example, if you write Vlookup with a cell containing the * or ? character you will get wrong results. As you have learned in the previous section Excel considers these two characters differently. So if you want to look up for a value LUCK*, you can use the tilde character followed by * to nullify the effects of this character. So you can use like this LUCK~*. We will go through an example for you to understand better.

Example: Open the file Chapter 2.4 - Vlookup Tilde.xlsx

Here we have some product ids ending with a question mark and a star, and you want to search for the product id to get the product name.

[example]

In this case, if you use product id as the lookup it will return an error because of the * and ?. So to nullify the effect we use the Tilde character. Enter the formula =VLOOKUP(G3&"~*",A2:B5,2,0) in G4 cell with the first parameter like this G3&"*". Now it will include all the * at the end of the product id and search. Here there is question mark also at the end, so if you enter a product id with a question mark, it will raise an error. To avoid that you can use an IF function clubbed with Right and Len functions to check whether the value ends with a star or question mark. And then you can write the two Vlookup formulas in If function for the star ending and question mark ending. Try writing this formula for yourself.

× DATE: Thu, Feb 06, 2020
× TIME: 9:38:44 pm
× BEHAVIOR:
on Twitter or sign-up for my monthly newsletter: