In Microsoft Excel, the VLOOKUP formula is one of the hottest topics that you can find on the internet. There are numerous articles on this topic discussing its functionality and advantages. As it is a crucial one, there is more to it. We can search for values using a lookup value. But, if your lookup value and searching column have different formats, it will cause an error. In this tutorial, you will learn to convert a number to text for the VLOOKUP function in Excel.
This tutorial will be on point with suitable examples and proper illustrations. So, stay with me.
Problem with the Text and Number Formats in VLOOKUP
Before starting, I assume you know well about the VLOOKUP function in Excel. We basically use a lookup value to search for particular values from the columns. Now, this seems pretty simple, right? But there are some issues.
If your lookup value and the column values are in a different format, it will cause an error.
Have a look at the dataset:
Here, we have a movie dataset. We used the following VLOOKUP formula to find the Movie and actor name. But, we got an error while doing that.
To get the Movie name:
=VLOOKUP(G4,$B$4:$D$12,2,FALSE)
To get the Actor name:
=VLOOKUP(G4,$B$4:$D$12,3,FALSE)
Now, that’s because of the text and number format. Our lookup value was in the number format and our lookup column has text format. That’s why it couldn’t give us the result we wanted. Now, we have to convert the number to get the actual result.
You can go two ways. One is to convert the lookup number to text in the VLOOKUP function. Or you can convert the whole column of text values to numbers using the paste special methods or other methods. But, in my opinion, it will be hectic and you don’t need to do that. You can easily solve the problem using the following two methods.
How to Convert Number to Text for VLOOKUP in Excel: 2 Ways
In the following sections, I will provide you with two methods to convert a number to text for the VLOOKUP function in Excel. I recommend you learn and apply all the methods to your dataset to convert. It will definitely develop your Excel knowledge. Let’s get into it.
1. Use TEXT Function to Convert Number to Text for VLOOKUP
Now, you can easily convert the number in the lookup value to text using the TEXT function in Excel. What it actually does is convert the lookup value to the text format. After that, the VLOOKUP function takes that as an argument and searches for the result.
The Generic Formula:
=VLOOKUP(TEXT(cell,0),table_array,column_index_number,FALSE)
Now. to get the Movie name:
=VLOOKUP(TEXT(G4,0),$B$4:$D$12,2,FALSE)
After that, get the Actor name using the following formula:
=VLOOKUP(TEXT(G4,0),$B$4:$D$12,3,FALSE)
As you can see, we used the TEXT function successfully to convert numbers to text for the VLOOKUP function in Excel.
2. Concatenate Empty String to Convert Number to Text
Now, you can also convert a number to text in Excel with apostrophes and ampersands. If you concatenate a number with an empty string(“”), it will convert that number to text format. Here, we will use that method in the VLOOKUP formula.
The Generic Formula:
=VLOOKUP(lookup_value&””,table_array,column_index_number,FALSE)
Here, by using the apostrophe with the ampersand sign, we converted the lookup value from number to text.
Now, to get the Movie name:
=VLOOKUP(G4&"",$B$4:$D$12,2,FALSE)
After that, get the Actor name using the following formula:
=VLOOKUP(G4&"",$B$4:$D$12,3,FALSE)
As you can see, we successfully converted the number to text in Excel with the apostrophe in the VLOOKUP formula.
Read More: How to Convert Number to Text in Excel with Apostrophe
💡 Important Tip
Now, if you are not sure whether the lookup value is in the numbers or text format, use the IFERROR function in the following formula:
=IFERROR(VLOOKUP(G4,$B$4:$D$12,2,FALSE),VLOOKUP(G4&"",$B$4:$D$12,2,FALSE))
Here, we try the VLOOKUP formula assuming that the lookup value and the first column in the table array are in numbers. If it returns an error, then it will try the next VLOOKUP formula. The next VLOOKUP formula will convert the number to text. After that, if this also fails, the VLOOKUP will throw the #N/A error.
Convert Text to Number for VLOOKUP Function in Excel
Now, you can also be in the opposite situation. That means you have the first column in the number format but your lookup value in the VLOOKUP function is in the text format. If so, it will look like the following:
Now, to solve this problem, you can use the VALUE function to convert text to numbers for the VLOOKUP formula in Excel.
The Generic Formula:
=VLOOKUP(VALUE(lookup_value),table_array,column_index_number,FALSE)
Now, the VALUE function converts a specific text string that describes a number into a numeric value. So, if your lookup value is in text format, the formula will convert that text to a number first. After that, it will run the entire VLOOKUP formula in Excel.
Now, to get the Movie name:
=VLOOKUP(VALUE(G4),$B$4:$D$12,2,FALSE)
After that, get the Actor name using the following formula:
=VLOOKUP(VALUE(G4),$B$4:$D$12,3,FALSE)
As you can see, we successfully used the VALUE function to convert text to numbers for the VLOOKUP formula in Excel. Give these a try in your Excel worksheet.
💬 Things to Remember
✎ If you are not sure about the formats, then wrap the VLOOKUP in the IFERROR function as we discussed earlier.
✎ Turn on the error checking to find the numbers stored as text.
Download Practice Workbook
Conclusion
To conclude, I hope this tutorial has provided you with a piece of useful knowledge on how to convert a number to text for the VLOOKUP function in Excel. We recommend you learn and apply all these instructions to your dataset. Download the practice workbook and try these yourself. Also, feel free to give feedback in the comment section. Your valuable feedback keeps us motivated to create tutorials like this.
Keep learning new methods and keep growing!
Related Articles
- Excel Convert Number to Text with Leading Zeros
- How to Convert Number to Text with Green Triangle in Excel
- How to Convert Number to Text and Keep Trailing Zeros in Excel
- Convert Number to Text without Scientific Notation in Excel
- How to Convert Number to Text with 2 Decimal Places in Excel
- Convert Number to Text with Commas in Excel
HI, I HAVE A VLOOKUP FORMULA [=IF(ISNA(VLOOKUP((VALUE($A42)),PROJECT_LIST!$A:$C,3,FALSE)),””,VLOOKUP((VALUE($A42)),PROJECT_LIST!$A:$C,3,FALSE)&” “)] ITS WORKING WHEN IT LOOKUP ON NUMBERS BUT TO LOOKUP ON LETTERS WITH NUMBERS IT SHOWS ERROR (#VALUE).
Hello ARCHIE,
I understand you wish to fix the #VALUE! error for VLOOKUP. In your case, the reason this is returning #VALUE! error for alpha-numeric values is that it is trying to convert the text values to numbers using the VALUE function. The VALUE function is designed to convert text that represents a number to an actual number. When it encounters alpha-numeric values, it cannot convert them to numbers, hence the error.
Fortunately, you can fix this by modifying the formula by replacing the VALUE function with the TEXT function. The TEXT function converts a value to text in a specific number format. Try the modified formula given below:
=IF(ISNA(VLOOKUP(TEXT($A42,"0"),PROJECT_LIST!$A:$C,3,FALSE)),"",VLOOKUP(TEXT($A42,"0"),PROJECT_LIST!$A:$C,3,FALSE)&" ")Let me know with a demo dataset if you still face issues. Good luck!
Note: Make sure data are sorted in ascending to descending order in the lookup table.
Regards,
Yousuf Khan