VLOOKUP for Text (Table of Contents)
Introduction to VLOOKUP For Text
VLOOKUP has served us in several ways in our daily work. We all know VLOOKUP can fetch the data based on the lookup value from the data table. LOOKUP value should be the same as it is in the data table, then only VLOOKUP can give us the value correctly; otherwise, it will return the error value as a result.
One such error case is when the numerical values are stored as text values in excel. This article will see how to deal with this kind of case and still get the job done.
Examples to Apply VLOOKUP Formula
We all know if the numerical values appear in excel are all based on the format applied to those numerical values.
Example #1 – Numbers Stored as Text Value
For example, look at the below image.
In Column B, Numerical Values are stored as Numbers, and in Column C, Numbers are stored as text values so we can get the total of the numbers. Let’s apply the SUM formula in cell B5.
After applying the formula, we will get the result.
Now let’s apply the formula in cell C5.
After applying the formula, the result is shown below.
This is the scenario in excel when the numbers stored as text values.
Example #2 – VLOOKUP Function to Extract the Data
In table 1, we have city code and pin code against city code. In table 2, we have only City Code and based on this; we need to extract the Pin code from table 2.
The first thing that comes to our mind is the VLOOKUP function; yes, you are correct; we can fetch the data by applying the VLOOKUP function. Let’s apply the VLOOKUP function to extract the Pin Code for each city.
We have got an error value of #N/A against all the city codes. This seems to be unrealistic at this point in time, isn’t it? One of the common problems in these cases is numbers are stored as text values.
4.9 (2,356 ratings)
View Course
You can say we can convert these numbers from text values to number values, yes we can, but still, we do not get the results. I think you must have encountered this kind of situation when the actual data is coming from different servers. Nothing to worry I will show you the way to get rid of this such situation. There are several ways you get rid of this.
Example #3 – Convert Text Values to Numerical Values using Paste Special Method
We can convert all the number looking text values to numerical values using Paste Special method.
Keep the VLOOKUP formula as it is already applied.
Enter number 1 in cell H1 and Copy the number.
After copying the cell H1, select the range D3 to D22.
After selecting the range D3 to D22, press ALT + E + S + M.
Once you complete this process, you should get the result with the VLOOKUP function.
Method #4 – Convert Text Values to Numerical Values using VALUE Function
If you are aware, we have a function called VALUE in excel. Using this function, we can convert number looking text values to number format in excel. Firstly before we supply the lookup value to the VLOOKUP function, we need to convert the numerical looking text value to the actual number, so nest the function VALUE inside the VLOOKUP to convert it to a number.
Open VLOOKUP & VALUE in the same formula as shown in the below image.
Select the lookup value.
Select the lookup table, mention column index number and range lookup type.
Now we should get values of pin code against each country code.
Method #5 – Convert Numerical Values to Text using TEXT Function
We have seen how to deal with when the lookup value is in text format. What if the data table itself is in text format, but your lookup values are in numerical format.
Open VLOOKUP & TEXT function together in cell E3 and select the lookup value as cell D3.
In order to convert the number value to text, mention the format text argument as zero (0).
After applying the VLOOKUP Formula answer is shown below.
As usual select remaining arguments of the VLOOKUP function and complete the formula. You should get the result for your VLOOKUP function.
Things to Remember About VLOOKUP For Text
- To convert numerical values to text values, we can use the TRIM function as well as the TEXT function.
- To convert the number looking text values to numerical values, we can use the VALUE function.
- In order to find the text values, errors turn on the error checking.
Recommended Articles
This is a guide to VLOOKUP For Text. Here we discuss how to use VLOOKUP For Text in excel along with practical examples and a downloadable excel template. You can also go through our other suggested articles –
