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â€ť.**

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

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.

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

**Similar Readings**

**VLOOKUP Not Working (8 Reasons & Solutions)****Excel LOOKUP vs VLOOKUP: With 3 Examples****INDEX MATCH vs VLOOKUP Function (9 Examples)****How to Vlookup and Sum Across Multiple Sheets in Excel (2 Formulas)****Excel VLOOKUP to Return Multiple Values Vertically**

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

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

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.

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

**VLOOKUP and Return All Matches in Excel (7 Ways)****How to VLOOKUP from Multiple Columns with Only One Return in Excel (2 Ways)****10 Best Practices with VLOOKUP in Excel****How to VLOOKUP Partial Text in Excel (With Alternatives)****VLOOKUP Partial Text from a Single Cell in Excel****VLOOKUP with Two Lookup Values in Excel (2 Approaches)**