Rifat Hassan
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.
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.
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.
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 VLOOKUP function.
Read More: VLOOKUP with Two Lookup Values in Excel (3 Simple Methods)
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
- Excel VLOOKUP to Find Last Value in Column (with Alternatives)
