# 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.
`=VLOOKUP("*Second World War*",B5:D15,2,FALSE)`

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

How Does the Formula Work?

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

We have used the Asterisk(*) Symbol on both sides of the text “Second World War” because we’re 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:D15 and returns the 2nd column data (Author).

• 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:
`=VLOOKUP("Harry Potter*",B5:D15,2,FALSE)`

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

• 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:
`=VLOOKUP("*"&B18&"*",B5:D15,2,FALSE)`

### 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.
`=VLOOKUP("JEN???",B5:D16,2,FALSE)`

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

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