Vlookup Partial Match in Excel

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.