In excel formulas; we can use the wildcard for partial matches to get the results. This can be used many functions where we do matching to get the results. Similarly, we can use the wildcard in vlookup formula. In this post I will explain you how to use vlookup wildcard in excel formula. You can also download the sample excel file for practice.

Vlookup function allows us to do the approximate match instead of exact match. To do the approximate match we can do following changes in the vlookup formula by mentioning TRUE at the last parameter.

In order to get the correct results in this, the data table should be sorted in ascending order and approximate match will give some results which may not be appropriate. To overcome this, we can use vlookup wildcard formula.

Following are the three wildcards which can be used in excel:

1. Asterisk (*): Use an Asterisk Find any number of characters after or before a text. You can use “excel*” to search “excelhub” or you can use “*hub” or you can also use “*celh*” to search the same.
2. Question Mark (?): Use a question mark to replace with one character while search. You can use “Organi?ation” to match both “Organisation” or “Organization”
3. Tilde (~): Tilde can nullify the impact of above two wildcards. In case if the lookup value itself consists of the wildcard, you can specify that with tilde like, “5~*house” in the place of “5*house”.

Example: How to use vlookup wildcard in excel

The use of wildcard in excel is explained with the example of a sales data of employees. The data table contains, Invoce No, Customer Name, Sales amount. In this data table the invoice number contains different format combined in one column. If you want to get the details based on the invoice number in a vlookup table, the normal vlookup function will return the error since it will not find the exact match as below.

To get the correct result with the employee id, we can use wild card Asterisk (*) in vlookup formula as below. This will match only invoice no. and will ignore anything after that and return the values in vlookup table.

Wild card question Mark  in vlookup

In the same example in the invoice number starts with GST but after that for some invoices “/” is used instead of “-“, therefore vlookup may not work properly in all cases. To avoid this, we can use “?” to replace “GST-“ or “GST/” like “????”.

Alternatively, we can also use “GST?” instead of “????” to resolve the issue.

One question mark will replace only one character, in case if we don’t know exactly how many characters it should replace, we can use “*”. In that case we can add “*” at the beginning as well as at the end in this example.

Wildcard in vlookup is really helpful when the data in data table is not standardized. This will make our job easy and quicker. You can try doing this with the example file which you can download from below link