How to use VLOOKUP to search text in Excel – 5 easy ways

Here's our step-by-step guide.

How to use VLOOKUP to search text in Excel

You can trust PC GuideOur team of experts use a combination of independent consumer research, in-depth testing where appropriate – which will be flagged as such, and market analysis when recommending products, software and services. Find out how we test here.

Last Updated on

Want to know how to use VLOOKUP to search text in Excel? We’ve got you covered right here.

Let’s say you have a product dataset with their codes, names, and categories. Now, you want to look up products using keywords. Manually looking through a huge dataset would take a lot of work. You could use the Ctrl+F function to search for text. But if you’re doing this for a range of lookup keywords, using Ctrl+F is not the way to go.

VLOOKUP is a handy formula to use in Excel, as its functionalities allow you to detect text errors and even report parts of text. In this guide, we’ll walk you through how you can use this function to search text in Excel.

How you can use VLOOKUP to search text in Excel

Here’s the scenario – we have a sample product dataset with their code, name, and category.  

Let’s explore the following five ways to use VLOOKUP to search for text in a dataset in Excel. 

  1. Searching for text using text via the VLOOKUP formula
  2. Finding text using cell address through the VLOOKUP formula
  3. Searching for the presence of text value among numbers using the VLOOKUP formula
  4. Using VLOOKUP to search for the corresponding text
  5. Using LEFT and RIGHT VLOOKUP functions to search for matching text

Method 1 – Searching for text using text via the VLOOKUP formula

We can use the VLOOKUP formula to search for specific text by entering the text in the formula. To proceed:

1. Choose a cell where you want the lookup output and then enter the following formula:

=VLOOKUP(“*{Text you want to search}*”, {Two column table range}, {1 or 2}, {FALSE})

=VLOOKUP(“*Soap*”, C7:D13, 1, FALSE)

Side-notes:

  • Here, you need to enter the table range of two columns since the formula only works for a 2 column dataset. 
  • 1 or 2 is the column index number which contains the text you want to search for. 
  • False or True is for an exact match or an approximate match. We put FALSE in the formula since we want an exact match of the text we entered.
A screenshot of a Microsoft Excel spreadsheet showcasing the powerful VLOOKUP function used to search and retrieve specific text.

2. Hit Enter to see the results.

A screenshot of a Microsoft Excel spreadsheet demonstrating the VLOOKUP function for searching text.

That was the easiest way to look for a specific text within a dataset using the VLOOKUP formula.

With this formula, you cannot use the Fill Handle drag tool to get results automatically in the remaining cells. For that, we use a formula with the cell address in place of the text.

Method 2 – Finding text using cell address through the VLOOKUP formula

We can use the VLOOKUP formula to search for specific text by entering the cell address in the formula. To proceed:

1. Choose a cell where you want the lookup output and then enter the following formula:

=VLOOKUP(“*”&{Cell Address}&”*”, {Two column table range}, {1 or 2}, {FALSE})

=VLOOKUP(“*”&F8&”*”, C7:D13, 1, FALSE)

A screenshot of a Microsoft Excel spreadsheet showcasing the VLOOKUP function being used to search text.

Method 3 – Searching for the presence of text value among numbers using the VLOOKUP formula

At times, due to errors, you might have a text value within numeric data. The VLOOKUP formula comes in handy to identify such issues. 

1. Enter the following formula in any cell and press enter: 

=VLOOKUP(“*”,{Lookup range},{1 or 2} ,{FALSE})

=VLOOKUP(“*”,B7:B13,1,FALSE)

A screenshot of a Microsoft Excel spreadsheet showcasing a VLOOKUP function used to search text.

This formula helps us to identify the error in the product codes. 

Method 4 – Using VLOOKUP to search for the corresponding text

This method uses the VLOOKUP formula to search for corresponding text in a dataset.  

1. Type this formula in your output table:

=VLOOKUP({String or numeric lookup value}, {Dataset range with $ sign}, {Column index number}, {TRUE or FALSE})

=VLOOKUP(F7, $B$7:$D$13, 2, FALSE)

Next, press Enter.

A screenshot of a Microsoft Excel spreadsheet showcasing the use of VLOOKUP function to search text.

2. Drag the Fill Handle tool to input the formula in the remaining cells.    

A screenshot of a Microsoft Excel spreadsheet demonstrating the VLOOKUP function to search text.

Note: Here, we’ve used the absolute cell reference using the $ sign ($B$7:$D$13) to keep it unchanged while dragging the Fill Handle to auto-fill the formula across the table. 

Method 5 – Using LEFT and RIGHT VLOOKUP functions to search for matching text

This method uses the VLOOKUP formula to search for text on the left and right in a dataset.  

5.1 Combining LEFT and VLOOKUP to search for codes

Imagine that the starting 3 digits of the product code indicate the product category. Now, if we have a set of product codes, we can extract the product category using the LEFT function in the VLOOKUP formula. 

1. Type this formula in your output table:

=VLOOKUP(VALUE(LEFT{Cell address of lookup value},{Number of digits}, {Dataset range with $ sign}, {Column index number}, {TRUE or FALSE})

=VLOOKUP(VALUE(LEFT(E7,3)), $B$7:$C$11, 2, FALSE)

Next, press Enter and drag the Fill Handle to auto-fill the formula across the table.

A screen shot of an Excel spreadsheet featuring the VLOOKUP function to search text.

5.2 Combining RIGHT and VLOOKUP to search for codes

The store updates out-of-stock product codes with product code names + OOS. 

In the product list, only 6-digit codes are allowed. The program automatically truncates the 3-digit product code on the left. 

This means that a simple VLOOKUP formula cannot work.

Using the RIGHT VLOOKUP function, the inventory manager can quickly scan through the out-of-stock products. 

1. Type this formula in your output table:

=VLOOKUP(RIGHT{Cell address of lookup value},{Number of digits}, {Dataset range with $ sign}, {Column index number}, {TRUE or FALSE})

=VLOOKUP(RIGHT(E7,6), $B$7:$C$13, 2, FALSE)

Hit Enter and use the Fill Handle to auto-fill the formula across the table:

A screen shot of a Microsoft Excel spreadsheet showcasing the VLOOKUP function being used to search text within the cells.

Note: If you’re using the LEFT and RIGHT functions for a numeric lookup value, add VALUE with the lookup value like this: VALUE(RIGHT(E7,6)). 

Wrapping up

We discussed five ways to use the VLOOKUP formula to search for text and numerical values in Excel. The easiest way is to enter the text in the formula. But Vlookup’s functionalities extend beyond just the find function, which the rest of the methods cover. 

Learn more about Excel with these helpful step-by-step guides: