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