Start Free Course
VLOOKUP-for-Text-Search-for-specific-text-using-wildcards-4

VLOOKUP for Text

Written by ExcelMojo Editorial Team ExcelMojo Editorial Team ExcelMojo editorial profile and article credentials. Full Bio
Reviewed by Dheeraj Vaidya, CFA, FRM Dheeraj Vaidya, CFA, FRM Reviewed for accuracy, clarity, and editorial standards. Full Bio
Updated Apr 29, 2026
Read Time 13 min

What Is VLOOKUP With Text?

The VLOOKUP with text is a function used to lookup data in the text format. We know that VLOOKUP is used to retrieve a value for the desired lookup value from the data table. To make sure the VLOOKUP function works correctly, the basic criteria is that the lookup value and format that we are searching for in the data table should be the same as in the data table.

However, when we deal with numerical lookup values, we may face the problem of lookup values being stored in text format and the lookup column in the data table stored in numerical format. In such cases, a regular approach of applying the VLOOKUP function won’t work, rather we must employ a different strategy for dealing with a numerical value stored as text.

📥Download the ready-to-use Excel template to practice this tutorial yourself.

Download Excel Template
Key Takeaways
  • The VLOOKUP with TEXT function returns the lookup value as a text value.
  • The VLOOKUP doesn’t work for various data types.
  • If the numerical value is stored as text, we can identify them using the ISNUMBER function.
  • The VALUE function returns the lookup value to the VLOOKUP function as a numerical value.
  • When we have to deal with both numerical as well as text stored numbers, we need to use the IFERROR function along with the combination of VLOOKUP and VALUE and VLOOKUP and TEXT.

Numerical Value Stored As Text

Let us understand the concept through a simple example of understanding numerical values stored as text.

For instance, look at the following data in Excel.

Frequently Asked Questions (FAQs)

1. How do I VLOOKUP text from another sheet?

Suppose you have the main table on one sheet and the result table on another sheet. In the result table sheet, we need to select the lookup value, and we need to select the table array from the main table worksheet to retrieve values for the respective lookup values.

2. Why is VLOOKUP for text not working?

The VLOOKUP function won’t work if the lookup value and the lookup value column in the main table are in a different format.

3. What are the arguments in VLOOKUP for text?

The VLOOKUP arguments are as follows.

VLOOKUP(lookup_value, table_array, col_ind_num, [range_lookup])

However, when we use the text-stored numerical values, we need to combine the lookup value argument with either VALUE or TEXT functions based on the format of the lookup value.

4. How Do I VLOOKUP Text and Numbers?

Sometimes, numbers are stored as text. So, in these cases, we need to use the VALUE function to convert the text-stored number to the numerical value.

Sometimes, in the main table itself, numerical values are stored as text, and in the result table, we have lookup values in numerical format. So, in these cases, we need to use the TEXT function to convert the lookup value to text format.

Download Template

📥Download the ready-to-use Excel template to practice this tutorial yourself.

Download Excel Template

This has been a guide to VLOOKUP for Text. Here we learn how to use VLOOKUP() to search strings in Excel, along with examples and downloadable excel template. You can learn more from the following articles –