The VLOOKUP function is arguably the most famous tool in Microsoft Excel. It allows you to search a large database for a specific value and return corresponding data from the same row. However, VLOOKUP requires exact matches by default. If you search for the company “Acme Corp”, but your database lists them as “Acme Corporation LLC”, a standard VLOOKUP will fail and return a frustrating #N/A error.
When dealing with messy datasets, inconsistent naming conventions, or partial information, you need a way to tell Excel to look for a “partial match”. You can achieve this by combining the standard VLOOKUP formula with Excel’s wildcard characters.
Understanding Excel Wildcards
In Excel formulas, a wildcard is a special character that acts as a placeholder for unknown text. For VLOOKUP, the most important wildcard is the asterisk (*).
The asterisk represents “any number of characters”.
- If you search for
"Acme*", Excel will find “Acme”, “Acme Corp”, and “Acme International”. It requires the word to start with “Acme” but does not care what comes after it. - If you search for
"*Acme*", Excel will find any cell that contains the word “Acme” anywhere inside it, even if it is surrounded by other words.
How to Hardcode a Wildcard into VLOOKUP
If you are typing your search term directly into the formula, adding a wildcard is incredibly simple.
Imagine you have a list of employees in Column A and their phone numbers in Column B. You remember an employee named “Smith”, but you cannot remember if it was John Smith or Jane Smith. You want to pull their phone number.
The standard syntax is =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]).
To use a wildcard, you simply include the asterisk inside the quotation marks with your search term.
=VLOOKUP("*Smith*", A2:B100, 2, FALSE)
This tells Excel: “Search the range A2:B100 for any cell containing the word ‘Smith’, regardless of what comes before or after it. When you find it, give me the value in the 2nd column.”
How to Use Wildcards with Cell References
Hardcoding the search term into the formula is rigid. Usually, you have a separate cell (e.g., D2) where users can type their search query, and the VLOOKUP references that cell. You cannot simply type *D2* into the formula, as Excel will literally search for the characters “D”, “2”, and the asterisks, rather than looking at the text inside cell D2.
To combine a cell reference with wildcards, you must use the ampersand (&) symbol, which concatenates (joins) text together.
Let’s say cell D2 contains the partial search term: Smith
You construct the formula by wrapping the cell reference in asterisks and ampersands:
=VLOOKUP("*" & D2 & "*", A2:B100, 2, FALSE)
Breaking Down the Logic:
"*": Start with an asterisk (meaning any text).&: Join it to…D2: The value inside cell D2 (which is “Smith”).&: Join it to…"*": End with an asterisk (meaning any text).
When Excel runs this formula, it pieces together the string *Smith* and searches the database for a partial match.
Important Limitations to Remember
While wildcards make VLOOKUP significantly more flexible, you must be aware of how the function behaves when it finds multiple matches.
VLOOKUP searches from the top of the database down to the bottom. It will always stop at the very first match it finds. If your database contains “John Smith” on row 10 and “Jane Smith” on row 45, the wildcard formula *Smith* will only return John’s phone number, completely ignoring Jane. Wildcards are best used when you are confident the partial string you are searching for is unique within the dataset (such as an invoice number with an unknown prefix).