VLOOKUP Partial Text from a Single Cell in Excel

Today I will be showing how you can use VLOOKUP to search for partial text in Excel.

One of the most important and widely used functions of Excel is VLOOKUP. 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.

VLOOKUP Partial Text (Quick View)

Download Practice Workbook

VLOOKUP Partial Text from a Single Cell in Excel

Here we have a data set with the Names, Authors, and the Book Types of some books of 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.

1. Using Asterisk Symbol (*)

First of all, let’s try to find out whether there is any book related to the Second World War in the data set.

If there is, then 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.

The Wildcard Character Asterisk (*) will come in handy here.

Read more: How to Perform VLOOKUP with Wildcard in Excel

You can use one Asterisk symbol for any number of unknown characters in the VLOOKUP formula.

Therefore, the formula to find out the author of the book with the text “Second World War” will be:

See, there is one book with the words “Second World War” in it.

And the author of that book is Winston Churchill.

Here we have used the Asterisk(*) Symbol in 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.

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

Similarly, you can also look for a book with the text “Freedom” at the end. This time use the

The formula with Asterisk (*) symbol in the beginning:

And if you have the partial text 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 F4, use the formula:

See, we have got the same name, Winston Churchill.

2. Using Question Mark (?)

To look up a value with a specific number of unknown characters with a text, you can use the Question Mark (?) in place of the Asterisk (*) symbol.

Here we’ve another data set with the Code Names, Names, and Salaries of some employees of a company named Mercury Group.

See carefully, 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.

So the formula to find out an employee with the code name starting with “JEN” will be:

See, 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”.

The formula will be:

And 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 F4, the formula to find the employee with the code name with “INA” will be:

Conclusion

Using these methods, you can use the VLOOKUP function of Excel to look for any partial text in any data set. Do you have any questions? Feel free to ask us.

Further Readings:

• VLOOKUP to Search Text in Excel (4 Easy Ways)
• VLOOKUP Fuzzy Match in Excel (3 Quick Ways)
• How to Use VLOOKUP for Partial Match
• How to Use VLOOKUP for Partial Match in Excel (4 Ways)
• VLOOKUP Partial Match Multiple Values (3 Approaches)
• 10 Best Practices with VLOOKUP in Excel
• Excel Partial Match Two Columns (4 Simple Approaches)
• Partial Match with IF in Excel (4 Basic Operations)
• How to Perform Partial Match String in Excel (5 Methods)
• Lookup Partial Text Match in Excel (5 Methods)