Return to Excel Formulas List

VLOOKUP Contains (Partial Match) – Excel & Google Sheets

Download Example Workbook

Download the example workbook

This tutorial will demonstrate how to perform a partial match VLOOKUP in Excel.

vlookup contains-partial match Main Function

VLOOKUP Function

The VLOOKUP Function is used to look up a value in the leftmost column of a range and returns the corresponding value from another column.

VLOOK

Partial Match VLOOKUP (begins with)

By combining a wildcard, using the asterisk symbol (*), with the VLOOKUP function, we can create a partial match VLOOKUP that returns the corresponding value for a partial lookup value. Let’s walk through an example.

We have a list of members names and their corresponding birthdays.

PVLU Table01

Supposed we are asked to get the member’s information using only part of their last name like so:

PVLU Table02

To accomplish this, we combine VLOOKUP with a wildcard in a formula like so:

PVLU

How does the formula work?

The formula concatenates the partial lookup value, “ake” in our example, with a wildcard. In this case, the VLOOKUP function will only match the characters in our partial lookup value, “ake”, and ignores the rest of the characters in the lookup range. We use the “&” symbol to concatenate the lookup value.

The position of wildcard in the formula determines the type of partial lookup performed like so:

  • “*”&<partial value>&”*” = contains match
  • <partial value>&”*” = begins with match
  • “*”&<partial value>= ends with match

Limitation: wildcards only work with VLOOKUP function in exact mode (0 or FALSE for the [range lookup] parameter)

 

VLOOKUP Contains (Partial Match) in Google Sheets

These formulas work exactly the same in Google Sheets as in Excel.

vlookup contains partial match Google Function