VLOOKUP Partial Text from a Single Cell in Excel

Quick View of VLOOKUP Partial Text

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)

Quick View of VLOOKUP Partial Text

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.

Data Set for VLOOKUP Partial Text from a Single Cell

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.

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:

=VLOOKUP("*Second World War*",B4:D22,2,FALSE)

Asterisk Symbol to VLOOKUP Partial Text from a Single Cell

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:

=VLOOKUP("Harry Potter*",B4:D22,2,FALSE)
[Used Asterisk(*) at the end].

Asterisk to VLOOKUP Partial Text from a Single Cell

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:

=VLOOKUP("*Freedom",B4:D22,2,FALSE)

Asterisk to VLOOKUP Partial Text from a Single Cell

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:

=VLOOKUP("*"&F4&"*",B4:D22,2,FALSE)

Asterisk to VLOOKUP Partial Text from a Single Cell

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.

Data Set to VLOOKUP Partial Text from a Single Cell

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:

=VLOOKUP("JEN???",B4:D23,2,FALSE)
[Used three Question Marks (???) for three unknown characters.]

Question Mark to VLOOKUP Partial Text from a Single Cell

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:

=VLOOKUP("???INA",B4:D23,2,FALSE)

Question Mark to VLOOKUP Partial Text from a Single Cell

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:

=VLOOKUP("???"&F4,B4:D23,2,FALSE)

Question Mark to VLOOKUP Partial Text from a Single Cell

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.

Rifat Hassan

Hello! Welcome to my profile. Here I will be posting articles related to Microsoft Excel. I am a passionate Electrical Engineer holding a Bachelor’s degree in Electrical and Electronic Engineering from Bangladesh University of Engineering and Technology. Besides academic studies, I always love to keep pace with the revolution in technology that the world is rushing towards day by day. I am diligent, career-oriented, and ready to cherish knowledge throughout my life.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo