VLOOKUP to Search Text in Excel (4 Easy Ways)

VLOOKUP Formula to Find Text in a Range of Numbers

Today I will be showing how to use VLOOKUP to search text in Excel.

One of the most amazing and widely used functions of Excel is VLOOKUP. The number of tasks that you can execute using this VLOOKUP function is enormous.

Today I will be showing how to use the VLOOKUP function to conduct multiple types of search operations on text values.


Download Practice Workbook


VLOOKUP Search Text in Excel


1. Searching for a Specific Text Using Wildcards

Here we’ve got a data set with the Names and Authors of some books in a bookshop called Martin Bookstore.

Data Set for VLOOKUP Search Text

You can use the VLOOKUP function to search for a name with a specific text in it using wildcards.

Use the specific text as the lookup_value and place an asterisk (*) symbol on both ends of it.

For example, to find out a book having the text “World War” in it, you can use this formula:

=VLOOKUP("*World War*",B4:C23,1,FALSE)

Using Wildcards for VLOOKUP Search Text

See, we have got the book “The History of the World War-2”.

And to use the cell reference in place of the specific text directly in the lookup_value, use the Ampersand (&) symbol to join the wildcards and the cell reference into one single text.

To find out a book having the text “World War” in it, with “World War” in cell E4, you can use the formula:

=VLOOKUP("*"&E4&"*",B4:C23,1,FALSE)

Using Wildcards with Cell References for VLOOKUP Search Text

See, we’ve got the same book, “The History of the World War-2”.

Read More: How to Perform VLOOKUP with Wildcard in Excel (2 Methods)


2. Searching if There is  Any Text Value in a Range of Numbers

Now we’ve got another data set. This time we have the Employee IDs and Employee Names of some employees in a company called Marco Group.

Data Set for VLOOKUP Search Text

In naked eyes, all the Employee IDs seem numbers. But still, there may be some text values hidden as numbers that we do not know.

We can use the VLOOKUP function with Wildcards to extract out if there is any text value in the range of numbers.

To extract out a text value, select a cella and enter this formula:

=VLOOKUP("*",B4:B23,1,FALSE)

VLOOKUP Formula to Find Text in a Range of Numbers

See, we’ve got 137. That means, 137 is inserted as a text value in the range of numbers.

And obviously, after converting 137 to numbers, you can use another VLOOKUP formula to check whether there is another text value or not.

Read More: VLOOKUP with Numbers in Excel (4 Examples)


Similar Readings


3. VLOOKUP with Numerical Lookup Values Inserted as Texts

This time we have the same data set with the IDs and the Names of the Employees.

But all the IDs are inserted as texts rather than numbers.

Data Set for VLOOKUP Search Text

As all the numbers are aligned left by default, so they are text values.

Now we have to find out the Names of some employees with some given IDs.

But the problem is that this time the IDs are provided as numbers.

Data Set for VLOOKUP Search Text

To solve this problem, you can concatenate an empty character (“”) with the cell reference of the numbers, to convert them to texts.

And then use them as the lookup_value of the VLOOKUP formula.

So the formula will be:

=VLOOKUP(E4&"",$B$4:$C$23,2,FALSE)

Drag this formula to the rest of the cells through the Fill Handle.

VLOOKUP Search Text by Converting Number to Text

See, we’ve got the Names of the given employees.

Note: Here we’ve used the absolute cell reference of the table_array ($B$4:$C$23), to keep it unchanged while dragging the Fill Handle.

Read More: What Is a Table Array in VLOOKUP? (Explained with Examples)


4. VLOOKUP Search for a Specific Portion of a Text Using Text Functions

We can use the LEFT or the RIGHT function of Excel in a combination with the VLOOKUP function to search for a specific portion of a text value.

Here we have a data set with the Mobile Telephone Prefixes and the Names of some countries of the world, along with some Numbers.

Data Set for VLOOKUP Search Text

You can use the LEFT function to extract the first four characters of the numbers, then use it in a VLOOKUP formula to know the name of the country.

So the formula will be:

=VLOOKUP(LEFT(E4,4),$B$4:$C$23,2,FALSE)

VLOOKUP with LEFT Function to Search Text

Note: Here we’ve used the absolute cell reference of the table_array ($B$4:$C$23), to keep it unchanged while dragging the Fill Handle.

Let us go back to our previous data set with the Employee IDs and Employee Names of the Marco Group.

This time a Code Name is given to a few employees, the last three characters of which are their IDs.

Data Set for VLOOKUP Search Text

We have to extract their original names from their code names.

You can use the RIGHT function to extract the last three characters of the code names, then use it in a VLOOKUP formula to know their original names.

So the formula will be:

=VLOOKUP(RIGHT(E4,3),$B$4:$C$23,2,FALSE)

VLOOKUP with RIGHT Function to Search Text

See, we have found out their original names.

Read More: Excel VLOOKUP to Find Last Value in Column (with Alternatives)


Conclusion

Using these methods. You can conduct various types of searches on a text string using the VLOOKUP function of Excel. Do you have any questions? Feel free to ask us.


Further Readings

Rifat Hassan

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