This Excel tutorial explains how to lookup partial text using Vlookup Function with Wildcard.
Excel Vlookup second matched value or specific occurence
Excel Lookup Function lookup multiple criteria not in the first column
Excel lookup Function lookup multiple criteria
Excel lookup partial text using Vlookup with Wildcard
Many people know how to use Vlookup but not many people know Vlookup allows Wildcard in the lookup value. Lets recap the syntax of Vlookup first.
Syntax of Vlookup
VLOOKUP( value, table, index_number, [not_exact_match] )
The “value” is the lookup value, where you can apply Wildcard. Wildcard is to use a dummy character to substitute the actual character in a text, so that you don’t need to search the actual text but lookup partial text.
Wildcard can also be used as criteria in many other functions, such as SUMIF, COUNTIF. If you have used Custom Formatting before, you may have known wildcard.
Vlookup supports the below three Wildcard characters. Note that Wildcard is case sensitive.
Wildcard | Meaning | Example |
* | Represents one or more characters (any character) | J* any text that starts with J *J starts with any text but ends with J *J* any text that has J in the middle |
? | Represents one character (any character) | J? 2 characters that start with J ?J 2 characters that end with J ?J? 3 characters with J in the middle |
~ | Treat * or ? as the actual character but not wildcard. Used ~ followed by * or ? | J~** any text that starts with J* ~**J any text that starts with * and ends with J ~?*~* any text that starts with ? and ends with * |
Example of lookup partial text using vlookup
Suppose you want to search the salary of employee using first name (John) instead of full name (John W).
Type the formula below.
Cell | Formula | Salary |
E3 | =VLOOKUP(D3&”*”,$A$3:$B$7,2,0) | 10000 |
E4 | =VLOOKUP(D4&”*”,$A$3:$B$7,2,0) | 20000 |
E5 | =VLOOKUP(D5&”*”,$A$3:$B$7,2,0) | 30000 |
E6 | =VLOOKUP(D6&”*”,$A$3:$B$7,2,0) | 40000 |
E7 | =VLOOKUP(D7&”*”,$A$3:$B$7,2,0) | 10000 |
Take Peter as an example. Searching D4&”*” is same as searching Peter* , which allows you to search partial text that start with John and ends with any text.
For John, because there are two persons with John as first name, the salary of the first John is returned. (10000)
Note that the last parameter [not_exact_match] should remain “0” (exact match), since “1” is for finding the closest “number”, not “text”.
What if source table contains partial text?
The example above demonstrates how to vlookup if lookup table contains partial text, but what if the source table contains partial text?
VLookup does not work for the above scenario. Instead, enter the below Array formula.
When you enter the formula in E3, do not type { }, press Ctrl+Shift+Enter to generate the { } automatically, and then autofill the rest of the Cells.
Cell | Formula | Salary |
E3 | {=INDEX(B$3:B$6,MATCH(TRUE,ISNUMBER(SEARCH(A$3:A$6,D3)),0))} | 10000 |
E4 | {=INDEX(B$3:B$6,MATCH(TRUE,ISNUMBER(SEARCH(A$3:A$6,D4)),0))} | 20000 |
E5 | {=INDEX(B$3:B$6,MATCH(TRUE,ISNUMBER(SEARCH(A$3:A$6,D5)),0))} | 40000 |
E6 | {=INDEX(B$3:B$6,MATCH(TRUE,ISNUMBER(SEARCH(A$3:A$6,D6)),0))} | 30000 |
Outbound References
http://msdn.microsoft.com/en-us/library/dd797422%28v=office.12%29.aspx
http://www.techonthenet.com/excel/formulas/vlookup.php
http://spreadsheeto.com/vlookup/