How to Use VLOOKUP to Search Text in Excel (4 Ideal Examples)

Get FREE Advanced Excel Exercises with Solutions!

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.


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.

VLOOKUP Search Text

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.

Use VLOOKUP Function to Check Presence of Text Value Among Numbers

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.

Find Names Using VLOOKUP with Numerical Lookup Value Inserted as Text

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

Apply LEFT and VLOOKUP Functions Together

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

A Suitable Alternative to VLOOKUP Function to Search Text in Excel

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

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
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

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo