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.

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.

• 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.

• 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:

• 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:

• 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:

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

Read More: Wildcard with VLOOKUP in Excel (6 Examples)

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 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.

• 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.

• 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:

Read More: How to Use VLOOKUP for Partial Match in Excel (4 Suitable 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

• How to Vlookup Partial Match for First 5 Characters in Excel
• Highlight Partial Text in Excel Cell (9 Methods)
• How to Use VLOOKUP Function with Exact Match in Excel
• Excel VLOOKUP for Partial Match in Table Array (3 Examples)
• Conditional Formatting for Partial Text Match in Excel (9 Examples)
• How to Use Named Range in Excel VLOOKUP Function
• [Fixed!] Excel VLOOKUP Partial Match Not Working