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.

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

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 Book Types of some books from a bookshop named Martinez Bookstore.

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)`

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

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.

As we can see from the dataset above, all the code names consist of 6 characters. Therefore, to find 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.]

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

• 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)`

Download Practice Workbook

Conclusion

Finally, this is the end of this article. 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

Get FREE Advanced Excel Exercises with Solutions!
Rifat Hassan

Rifat Hassan, BSc, Electrical and Electronic Engineering, Bangladesh University of Engineering and Technology, has worked with the ExcelDemy project for almost 2 years. Within these 2 years, he has written over 250 articles. He has also conducted a few Boot Camp sessions on effective coding, especially Visual Basic for Applications (VBA). Currently, he is working as a Software Developer to develop and deploy additional add-ins to enhance the customers with a more sophisticated experience with Microsoft Office Suits,... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF