Vlookup on Partial Matches in Excel

How to perform a VLOOKUP() search when there is only a partial match on the lookup value in the formula - this allows you to match the first part of a cell, the last part of a cell, or the middle of a cell when using Vlookup formulas.

I'll show you many examples for returning values from data sets based on partial matches that are made using the Vlookup function in Excel. To do this, we will use slightly more complex versions of the basic Vlookup function, and I will show you many examples of performing different partial matches within the spreadsheet.

Some of the examples include:
- matching parts of a cell
- matching specific text combined with optional text
- matching specific text with any individual letter after it or before it
- matching wildcard characters in the spreadsheet

The Vlookup function is a powerful function that can be used in many ways to improve the usefulness of your spreadsheet and, performing partial match searches is one of those ways.

The techniques shown in this tutorial serve the benefit of reducing the amount of data cleaning and augmenting that you have to do when you work with large data sets, particularly those that are imported into Excel.

I hope you enjoy this tutorial!

Stay safe!

#excel #tutorial #howto

[toxiqrox]

I didn't start from the beginning which I should have. I almost understood this, you have a very pleasant speaking voice and make it easier than many tutorials I have seen

[jamlecknjagi]

I had watched several tutorials on this task. Some abit complex. But this one is so simple and gives instant results.

[zaighamuddinfarooqui]

Videos seems to be discussed an ordinary matter but watching videos discloses that the matter is "extraordinary". 👍

[IvanCortinas_ES]

Excellent explanation about the use of wildcards in the function VLOOKUP in Excel. Thank you!!!

[WST]

Thank you so much. I have been searching so long days a solution for these types of problems. You make it clear and easy. Thank you so much

[rainewhite]

This was super helpful, been looking so long for a clear guide on how to understand this lookup. :-)

[wayneedmondson]

Great.. these are fun.. can do lots of creative things with wildcards. Thanks for the video! Thumbs up!!

[darrylmorgan]

Great Stuff..Really Nice Tutorial Using Wild Card Characters.Thank You Sir :)

[jeffheadley]

I am working on trying to figure a Vlookup to pull info from multiple different sheets/tabs in one document. Essentially I have a sheet that is showing which parts are needing to be ordered. some tabs have parts in them that begin with the letter P to signify they are purchased parts. is there a way to look up the part number and quantity from all sheets in the document, and display the part # and quantity on the "Order Parts" tab/sheet?

[gregwmanning]

I knew wildcards were powerful but struggled using them, by not understanding the rules obviously. Great explanation but leaves me with more questions (situations to practice!) like combining wildcards: xxxx?* at least one character after and *?xxxx at least one character before. Also how use tilda when using cell references inplace of hard coding a text string.
Always more to learn. Thanks again.

[natashajannah]

Do I have to add a column with the numbers? Example. In your example you have column E

[SonnyLetourneau]

Thank you! How can I do to find for example: "Sonic Temple" in a list where it could appear as "Sonic Temple Event" . And what if we want to see it like word by word. Example I want to search any of those 3 words: Sonic Temple Festival in a list where it could be written: Temple Festival, Or Temple Event or Sonic Bloom. How can I get a return on all of these words? :) thanks

[amishpatel]

Hello, I need your tip for this unusual scenario. I am looking up a value from one sheet in another sheet. Source value is CF19724071291022144694151. Target sheet contains the lookup value in the middle of a cell starting preceded by an *, the match should return a value. How do i construct the formula. My formula is not returning value all the time. Not sure why. Thanks for your time and attention

[betho]

Excellent video, thank you!! I'm trying to do a partial vlookup across data array that sometimes has an asterisk. Example, I'm trying to match up 'ABC Moving' in sheet A with 'ABC*Moving' in sheet B. If I'm doing the vlookup from sheet B to A, it finds the match; however, if I'm doing it from A to B, it won't find the match.

[maryannepoatsy]

Great information. These formulas work to return the first value in a list. But, what could you use to find all values that meet the lookup criteria?

[vikaasb]

Wonderful! I liked this wild cards character mention..however I would appreciate of u could explain use of Index Match more in this reference as VLOOKUP only search data right of the look up value ijn source sheet.Please explain keeping in mind the absolute ref as well. It confuses me alot.

[ahoth]

Please can you show us how to get data using vlock up if the main reference duplicate in same colume , , yours truly

[smhameed]

This is not working for me . Can you please help me. In B column there is a "rite aid" and in other column it is" rite aid corporation"

[cathysechrist]

Need to match a partial inside ( ) Example need to find L1 inside parentheses (L1) so I can match L1 to a table. Could be 2 or 3 characters inside the () (L1) or (L10) Help! :)

[hariomgupta]

Sir how can use this formula to many partial text condition