Vlookup Partial Match in Excel

Add to Favorites

Return Vlookup results on partial matches of a cell's contents. You could type the start, end, or middle of a lookup value and still return a result.

I assume that you are already familiar with Vlookup, if you aren't, then checkout our tutorials on lookup functions in Excel.

Sections:

Vlookup Match the Start of a Cell

Vlookup Match the End of a Cell

Vlookup Match Anywhere in the Cell

Notes

Vlookup Match the Start of a Cell

=VLOOKUP(A5 & "*",A1:B3,2,FALSE)

Result:

This works just like a regular Vlookup except for the asterisk.

The lookup value argument looks like this: A5 & "*"

It is the "*" that comes after the lookup value that makes this match the word "red" regardless of what comes after this word. The & (ampersand) is what we need to use to "attach" the lookup value to the asterisk symbol.

The asterisk must be surrounded by double quotation marks.

Also, the range_lookup must be set to False for this kind of Vlookup to work.

Vlookup Match the End of a Cell

=VLOOKUP("*" & A5,A1:B3,2,FALSE)

Result:

The asterisk at the start of the lookup_range argument is what makes this match the lookup value regardless of what comes before it in the cell.

You must always put the asterisk in this form "*" surrounded by double quotation marks and "attach" it to the lookup value by using the & (ampersand) character.

The range_lookup must be set to False for this kind of Vlookup to work.

Vlookup Match Anywhere in the Cell

=VLOOKUP("*" & A7 & "*",A1:B3,2,FALSE)

Result:

This Vlookup matches the first cell that contains the lookup value, regardless of where that value is found within the cell.

To make this example return 3, I changed the lookup value to green and put that value in cell A3, otherwise, a search for red would have returned 1 since it would match the contents in cell A1.

This version of the Vlookup function has two asterisks that surround the lookup_value; this is what allows a match to happen in this manner. The asterisks must be in this form: "*" with double quotation marks around them and they must be "attached" to the lookup value using the & (ampersand) character.

The range_lookup must be set to False for this kind of Vlookup to work.

Notes

This is a great way to use the Vlookup function in Excel but, remember, it will only return the first match that is made in the data set. As such, this type of lookup will not always be useful.

Download the attached file to work with these examples in Excel.


Excel Function: VLOOKUP()
Downloadable Files: Excel File

Similar Content on TeachExcel
Partial Match Lookup with Numbers in Excel
Tutorial: Perform lookups on numbers with partial matches. For instance, find the first number that...
Wildcards in Excel
Tutorial: Wildcards are characters that allow you to make more robust functions, searches, and filt...
Best Lookup Formula in Excel - Index and Match
Tutorial: A lookup using INDEX and MATCH is like a VLOOKUP without the restrictions.  Index and Mat...
How to use the Vlookup Function in Excel
Tutorial: Full explanation of the Vlookup function in Excel, what it is, how to use it, and when you...
How to use Vlookup Across Multiple Worksheets in Excel
Tutorial: This tutorial shows you how to use the Vlookup function across multiple worksheets within ...
Easily Input Complex Functions in Excel
Tutorial: In this tutorial I am going to show you how to easily input complex Functions in Excel. To...