VLOOKUP Contains (Partial Match) – Excel & Google Sheets

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

AutoMacro - VBA Code Generator

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

Excel Practice Worksheet

Practice Excel functions and formulas with our 100% free practice worksheets!

  • Automatically Graded Exercises
  • Learn Excel, Inside Excel!