In this Article
This tutorial will demonstrate how to perform a partial match VLOOKUP in Excel.
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.
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.
Supposed we are asked to get the member’s information using only part of their last name like so:
To accomplish this, we combine VLOOKUP with a wildcard in a formula like so:
|
1 |
=VLOOKUP(“*”&<Partial Value>&"*",<lookup range>,<match column position>,0) |
|
1 |
=VLOOKUP($G$1&"*",$B$1:$D$7,1,0) |
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.
