One of the most important and widely used functions in **Excel **is **the** **VLOOKUP function**. In this article, I will demonstrate the use of the **VLOOKUP **function to search text values with **4 **ideal examples.

**Table of Contents**hide

**Download Practice Workbook**

You can download the practice workbook from here.

## 4 Ideal Examples of Using VLOOKUP to Search Text in Excel

In this section, I will show **4 **ideal examples of using the **VLOOKUP **function to search text values in **Excel**.

### 1. Apply VLOOKUP Function to Search for Specific Text in Excel

We can use partially matched text to find data from a range of cells in **Excel**. For demonstration, I have introduced a dataset containing **Name of Book**, **Author **and I will show the way to find the book name by inserting a partial text of the book name.

Let’s follow the procedures below to learn the method.

- Firstly, write the following formula in
**Cell F5**.

`=VLOOKUP("*West Wind*",B5:C16,1,FALSE)`

- Then, press
**Enter**. - Instantly, we will see the
**Book Name**matched with the text in the argument of the**VLOOKUP**function.

In the formula,

**“*West Wind*”**is the lookup value.**B5:C16**is the lookup range.**1**denotes the column number in the table to search for.**False**denotes the match should be exact.

- Similarly, we can change the lookup value with cell reference also.
- For that, just insert the following formula instead.

`=VLOOKUP("*"&E6&"*",B5:C16,1,FALSE)`

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

### 2. Use VLOOKUP Function to Check Presence of Text Value Among Numbers

It’s possible to find a hidden text value among numbers with the help of the **VLOOKUP **function. In the dataset, I have included **Employee ID **and the column contains numbers as well as a hidden text value. Let’s follow the steps given below to check the presence of text value among numbers.

- Firstly, type the following formula in
**Cell E5**.

`=VLOOKUP("*",B5:B16,1,FALSE)`

- Simultaneously, press
**Enter**to see the text value among the numbers. - Here,
**137**was stored as a text value.

**Note:**In the argument of the

**VlOOKUP**function we used

**“*”**as the lookup value which denotes any text value.

**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. Find Names Using VLOOKUP with Numerical Lookup Value Inserted as Text

We can use a number as the lookup value and find the corresponding text value from a table. In the dataset, we will find **Employee Name **by using** Employee ID**. Here, the **Employee IDs **are the numerical lookup value but they are stored as texts. So, let’s walk through the procedures below to find the solution.

- First, type the following formula in
**Cell F5**.

`=VLOOKUP(E5&"",$B$5:$C$16,2,FALSE)`

- Next, press
**Enter**.

- After that, use the
**AutoFill**option to see the results for the cells below.

**Note:**Here we’ve used the

**absolute cell reference**of the array (

**$B$5:$C$16**), to keep it unchanged while dragging the

**Fill Handle**.

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

### 4. Utilize LEFT & RIGHT Functions with VLOOKUP to Find Text

Here, I will show the use of the **LEFT **& **RIGHT **functions of **Excel **along with the **VLOOKUP **function to search text value.

#### 4.1 Apply LEFT and VLOOKUP Functions Together

Let’s use the **LEFT **function first to find text in Excel. Follow the steps given below.

- First, write the following formula in
**Cell F5**.

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

- Then, hit
**Enter**. - Further, use the
**Fill Handle**to see the results for the cells below.

In the formula,

- The
**LEFT**function takes**4**left digits from the value of**Cell E5**which in turn acts as a lookup value for the**VLOOKUP**function. - As a result, it returns the name of the country that matches the lookup value in the lookup array.

#### 4.2 Combine RIGHT and VLOOKUP Functions

In a similar fashion, we can use the** RIGHT **function with the **VLOOKUP **function to search text. Let’s follow the given steps.

- First, write the following formula in
**Cell F5**.

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

- Then, hit
**Enter**. - Further, use the
**Fill Handle**to see the results for the cells below.

In the formula, the **RIGHT **function takes **3** right digits from the value of **Cell E5** which in turn acts as a lookup value for the **V****LOOKUP **function.

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

## A Suitable Alternative to VLOOKUP Function to Search Text in Excel

We can use the **INDEX **& **MATCH **functions together to do the same task as the **VLOOKUP **function to search text. Let’s follow the steps given below.

- First, write the following formula in
**Cell F5**.

`=INDEX($B$5:$B$16,MATCH("*"&E5&"*",$B$5:$B$16,0))`

- Then, press
**Enter**. - Finally, we will see the searched text value instantly.

In the formula,

**MATCH(“*”&E5&”*”,$B$5:$B$16,0):**This part gives the row number from**$B$5:$B$16**which matches value from**E5**.- After that, the
**INDEX**function takes the output from the**MATCH**function and finds the text value.

## Conclusion

The **VLOOKUP **function has a lot of uses. Obviously, searching text value is one of the uses. Here, I have shown **4 **ideal examples of using the **VLOOKUP **function to search for text value. Furthermore, I have also added the practice workbook at the beginning of the article. So, go ahead and give it a try. If you have any queries, please leave a comment. Visit our **ExcelDemy Website **for more articles regarding **Excel**.

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