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.

**Table of Contents**hide

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

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

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

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

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

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

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.

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

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.

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

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

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

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

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

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)**

See, we have found out their original names.

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

**VLOOKUP to Find Duplicates in Two Columns****10 Best Practices with VLOOKUP in Excel****How to Use VLOOKUP for Partial Match in Excel (4 Ways)****How to Find Value in Column Using VBA in Excel (4 Ways)****Use of VLOOKUP in VBA to Find Values from Another Worksheet in Excel****How to VLOOKUP Partial Text in Excel (With Alternatives)**