Excel lookup partial text using Vlookup with Wildcard

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).

lookup_partial_text_01

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?

lookup_partial_text_02

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/

 

Leave a Reply

Your email address will not be published.