Exceljet

Quick, clean, and to the point

Partial match with VLOOKUP

Excel formula: Partial match with VLOOKUP
Generic formula 
=VLOOKUP(value&"*",data,column,FALSE)
Explanation 

If you want to retrieve information from a table based on a partial match, you can do so using VLOOKUP in exact match mode, and wildcards. In the example shown, the VLOOKUP formula looks like this:

=VLOOKUP(val&"*",data,2,FALSE)

In this formula, val is a named range that refers to H2, and data is a named range that refers to B3:E102. Without named ranges, the formula could be written like this:

=VLOOKUP($H$2&"*",$B$3:$E$102,2,FALSE)

How this formula works

VLOOKUP supports wildcards, but only in "exact match" mode. To set exact match, make sure you supply the 4th argument as FALSE or 0.

In this case, we are supplying the lookup value as val&"*". We are using the ampersand (&) to concatenate the value in H2 with an asterisk (*).  If we type in a string like "Aya" into the named range val (H2), we are giving VLOOKUP "Aya*" as the lookup value. Placing the wildcard at the end results in a"begins with" match. This will cause VLOOKUP to match the first entry in column B that begins with "Aya".

Wildcard matching is convenient, because you don't have to type in a full name, but note that you have to be careful of duplicates or near duplicates. For example, in the table there is a "Bailer" and a "Bailey" so typing "Bai" will give you the first match, even though there are two.

Contains type match

If you need a "contains type" match (i.e. the search string can appear anywhere in the lookup values), you'll need to use two wildcards like this:

=VLOOKUP("*"&val&"*",data,2,FALSE)

Note: you must set exact match mode using FALSE or 0 for the last argument in VLOOKUP when using wildcards.

Author 
Dave Bruns

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.