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

We have a data set with the Names, Authors, and Book Types of some books from a bookshop named Martinez Bookstore. We’ll look for a partial text match within this table.

Method 1 – Use the Asterisk Symbol (*) to VLOOKUP Partial Text

We need books containing “Second World War” in the title and will get all the authors of said books.

Steps:

• We put the lookup value (“Second World War” in cell B18.
• In cell C18, use the following formula.

• There is one book with the words “Second World War” on the list and the author of that book is Winston Churchill. 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.
• To look for the author of a book with the text “Harry Potter” in the beginning, you can use the following formula:

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

• 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.
• The text “Second World War” is in cell B18, so use this formula:

Read More: How to Vlookup Partial Match for First 5 Characters in Excel

Method 2 – Use the Question Mark (?) to VLOOKUP Partial Text

We have another data set with the Code Names, Names, and Salaries of some employees. 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.

Steps:

• Apply the following formula.

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

• 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 Perform VLOOKUP with Wildcard in Excel

Download the Practice Workbook

Further Readings

• Use VLOOKUP to Find Multiple Values with Partial Match in Excel
• Excel VLOOKUP for Partial Match in Table Array
• How to Use Excel VLOOKUP to Find the Closest Match
• How to Use VLOOKUP to Find Approximate Match for Text in Excel

<< Go Back to VLOOKUP Partial Match | Excel VLOOKUP Function | Excel Functions | Learn Excel