Vlookup Partial Match in Excel

Add to Favorites
Author: | Edits: don

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)

45accb280c25d2bf87ad5da9aab13195.png

Result:

e3a0a4f64828dc24c44e288d64ed9267.png

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)

65e489e093798640539d330742377069.png

Result:

dcff19aa726fab961a0871926e2d5309.png

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)

a61fc84c2d9579d82b3f1f445de30995.png

Result:

ea5d595c044efdfbeeaf8c313a1807d2.png

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 1, Excel File 2

Question? Ask it in our Excel Forum


Our Excel Courses

  • Skill Level: All Levels
  • |
  • 5 hours+

How to make a fully featured professional form in Excel that is unbreakable. This includes how to use the form to store, view, edit, and delete data from a data storage worksheet.

  • Skill Level: All Levels
  • |
  • 2 hours

Send Emails from Excel using VBA and Macros. This course starts from the Basics and builds up to more advanced examples with attaching workbooks, worksheets, PDF's, automatically sending emails, including a signature, error handling, increasing speed, and more.

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...
Vlookup Date Picker in Excel (Dynamic)
Tutorial: Make a dynamically updating vlookup date picker for excel that allows you to choose a date...
How to Quickly Find Data Anywhere in Excel
Tutorial: Finding specific records and/or cells is easy when using the Find tool in Excel. It is lo...
VLOOKUP() Function in Excel
Tutorial: Full explanation of the Vlookup function in Excel, what it is, how to use it, and when yo...
Best Lookup Formula in Excel - Index and Match
Tutorial: A lookup using INDEX and MATCH is like a VLOOKUP without the restrictions.  Index and Ma...
Tutorial Details
Excel Function: VLOOKUP()
Downloadable Files: Excel File 1, Excel File 2
Similar Content
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...
Vlookup Date Picker in Excel (Dynamic)
Tutorial: Make a dynamically updating vlookup date picker for excel that allows you to choose a date...
Excel Forum