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

## VLOOKUP to Search Text: 4 Ideal Examples

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 the 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.
- For that, just insert the following formula instead.

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

**Read More: **How to Use VLOOKUP Function with Exact Match in Excel

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

### 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 the Employee Name by using the 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**.

### 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: **How to Use VLOOKUP with Two Lookup Values in Excel

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

**Download Practice Workbook**

You can download the practice workbook from here.

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

## Further Readings

- How to Apply Double VLOOKUP in Excel
- How to Use VLOOKUP to Find Duplicates in Two Columns
- How to Find Second Match with VLOOKUP in Excel
- VLOOKUP and Return All Matches in Excel
- VLOOKUP Fuzzy Match in Excel
- Excel VLOOKUP to Find Last Value in Column
- How to Apply VLOOKUP by Date in Excel
- Return the Highest Value Using VLOOKUP Function in Excel
- VLOOKUP with Numbers in Excel