Partial match with VLOOKUP
There are times when you need Excel to retrieve data based on partial information. To solve the problem, you can use a VLOOKUP formula together with wildcard characters - the asterisk (*) and question mark (?).
How to perform a partial match with VLOOKUP?
To get the information about the products and sales made by the salesman, whose name contains “ale”, from the table as shown above, you should know how to use the wildcard – the asterisk (*), which matches any number of characters. In the case above, you can put “ale” between two asterisks ("*ale*") to match all the names that contain ale. After we grasp the usage of wildcards, we can use the VLOOKUP function to apply a partial match.
Tips for other cases: Match the names that start with ale, use "ale*"; Match the names that end with ale, use "*ale"; Match the names that has ale as their 2-4th letters, use "?ale*". Note that one question mark matches one character.
To get the information about the products and sales made by the salesman, whose name contains “ale”, please copy or enter the formulas below in the cells G7 and G8, and press Enter to get the results:
Cell G7 =VLOOKUP("*ale*",B5:D11,2,FALSE)
Cell G8 =VLOOKUP("*ale*",B5:D11,3,FALSE)
Or, use a cell reference to make the formulas dynamic:
Cell G7 =VLOOKUP("*"&G5&"*",B5:D11,2,FALSE)
Cell G8 =VLOOKUP("*"&G5&"*",B5:D11,3,FALSE)
√ Note: To stick a cell reference and text together, you must use ampersand (&) to join them. And the text should be enclosed in double quotes.
Explanation of the formula
Here we use the formula below as an example:
- The lookup value "*"&G5&"*" ("*ale*") can match any names that contain the string “ale”, no matter what position the string is in the names.
- The range_lookup FALSE asks the VLOOKUP function to find the exact match.
- The VLOOKUP function will then return the value that matches "*"&G5&"*" and is in the 2nd column of the range B5:D11, which is Ruler, Glue, Notebook.