How to Use VLOOKUP to Find Partial Text from a Single Cell

Today I will be showing how to VLOOKUP partial text from a single cell in Excel. As we know, the VLOOKUP function is one of the most important and widely used functions of Excel. We can use the VLOOKUP function of Excel to look for any data related to any value from a range of cells in Excel. Today I will be showing how you can use the VLOOKUP function to look for a partial text in a range of cells.


Download Practice Workbook


2 Easy Methods to Use VLOOKUP to Find Partial Text from a Single Cell

In this section, I will demonstrate 2 straightforward methods to use the VLOOKUP function to search partial text from a single cell. To illustrate that, here we have a data set with the Names, Authors, and the Book Types of some books from a bookshop named Martinez Bookstore.

vlookup partial text from a single cell

Our objective today is to use the VLOOKUP function of Excel to look for a partial text from this data set. So let’s check out the 1st method.


1. Use of Asterisk Symbol (*) to VLOOKUP Partial Text

Here we will try to find out whether there is any book related to the Second World War in the data set. If there is, then we will find who the author is. That means we need a book with the text “Second World War” in the data set. There may be any number of characters before the text “Second World War”, or after the text. We do not know exactly. In this situation, the Wildcard Character Asterisk (*) will come in handy here.  You can use one Asterisk symbol for any number of unknown characters in the VLOOKUP formula. To apply the Asterisk symbol, follow the steps below.

Steps:

  • On cell F5, write down the following formula.
=VLOOKUP("*Second World War*",B5:D15,2,FALSE)

  • Here, there is one book with the words “Second World War” on the list and the author of that book is Winston Churchill. Thus the result we got is Winston Churchill.

How Does the Formula Work?

  • VLOOKUP(“*Second World War*”,B5:D15,2,FALSE)

Here we have used the Asterisk(*) Symbol on both sides of the text “Second World War” because we were looking for a book name with unknown characters on both sides of the text. The VLOOKUP function searched this piece of text (Second World War) in the 1st column (Name of the Book) of the array of B5:D5 and returns the 2nd column data (Author) which is Winston Churchill

  • To look for a text with unknown characters on a specific side of the text, use the Asterisk(*) symbol only on that side.
  • For example, to look for the author of a book with the text “Harry Potter” in the beginning, you can use the  following formula:
=VLOOKUP("Harry Potter*",B5:D15,2,FALSE)
[Used Asterisk(*) at the end].

  • Similarly, you can also look for a book with the text “Freedom” at the end. This time use the formula with the Asterisk (*) symbol in the beginning:
=VLOOKUP("*Freedom",B5:D15,2,FALSE)

  • On the other hand, if you have the piece of text to be searched in another cell, use the Ampersand (&) symbol to join the Asterisk (*) symbol with the text.
  • For example, if the text “Second World War” is in cell B18, use the formula:
=VLOOKUP("*"&B18&"*",B5:D15,2,FALSE)

Use of Asterisk Symbol (*) to VLOOKUP Partial Text

  • Consequently, we can see that we have got the same name, Winston Churchill in the result.

Read More: VLOOKUP with Wildcard in Excel (3 Methods)


Similar Readings


2. Use of Question Mark (?) to VLOOKUP Partial Text

The previous method is usually applied when the number of characters before or after the searched text is unknown. However, when we know the number of characters, we can use the Question Mark(?) instead of the Asterisk symbol(*). To illustrate the 2nd method, here we’ve another data set with the Code Names, Names, and Salaries of some employees of a company named Mercury Group.

Use of Question Mark (?) to VLOOKUP Partial Text

As we can see from the dataset above, all the code names consist of 6 characters. Therefore, to find out an employee with “JEN” at the beginning of the code name, exactly 3 more unknown characters will remain. We can use the Question Mark (?) in this case. To apply for a particular code name such as “JEN”, follow the steps below.

Steps:

  • To find out an employee with a code name starting with “JEN”, apply the following formula.
=VLOOKUP("JEN???",B5:D16,2,FALSE)
[Used three Question Marks (???) for three unknown characters.]

Use of Question Mark (?) to VLOOKUP Partial Text

  • Consequently, we can see that there is one employee with the code name starting with “JEN”, Jennifer Marlo.
  • Similarly, we can find out an employee with the code name ending with the letters “INA” by applying the following formula.
=VLOOKUP("???INA",B5:D16,2,FALSE)

  • In addition to that, if you have the partial text in a cell, use the Ampersand (&) symbol to join the partial text with the Question Marks (?).
  • For example, if you have “INA” in cell B19, the formula to find the employee with the code name “INA” will be:
=VLOOKUP("???"&B19,B5:D16,2,FALSE)

Use of Question Mark (?) to VLOOKUP Partial Text

Read More: How to Use VLOOKUP for Partial Match in Excel (4 Ways)


Conclusion

Finally, this is the end of this article. Hopefully, by using these methods, you can utilize the VLOOKUP function of Excel to look for any partial text in any data set. Moreover, if you have any questions, feel free to ask us.


Further Readings

Rifat Hassan

Rifat Hassan

Hello! Welcome to my profile. Here I will be posting articles related to Microsoft Excel. I am a passionate Electrical Engineer holding a Bachelor’s degree in Electrical and Electronic Engineering from the Bangladesh University of Engineering and Technology. Besides academic studies, I always love to keep pace with the revolution in technology that the world is rushing towards day by day. I am diligent, career-oriented, and ready to cherish knowledge throughout my life.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo