How to Convert Number to Text for VLOOKUP in Excel (2 Ways)

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


Download Practice Workbook

Download this practice workbook.


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:

Problem with the Text and Number Formats in VLOOKUP

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


2 Ways to Convert Number to Text for VLOOKUP in Excel

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)

excel TEXT Function to Convert Number to Text for VLOOKUP

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.

Read More: How to Convert Numbers to Texts/Words in Excel


Similar Readings:


2. Concatenate Empty String to Convert Number to Text

Now, you can also convert a number to text in excel with apostrophe and ampersand. 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)

excel Convert Number to Text for VLOOKUP

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.

Related Content: Excel VBA: Convert Number to Text with Format (A Total Guide)


💡 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:

Convert Text to Number for VLOOKUP Function in Excel

Now, to solve this problem, you can use the VALUE function to convert text to number 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)

Convert Text to Number for VLOOKUP Function in Excel

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 number for the VLOOKUP formula in Excel. Give these a try in your Excel worksheet.

Read More: How to Convert Text to Numbers in Excel


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


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.

Don’t forget to check our website Exceldemy.com for various Excel-related problems and solutions.

Keep learning new methods and keep growing!


Related Articles

Shanto

Shanto

Hello! I am Shanto. An Excel & VBA Content Developer. My goal is to provide our readers with great tutorials on various Excel-related problems. I hope our easy but effective tutorials will enrich your knowledge. I have completed my BSc in Computer Science & Engineering from Daffodil International University. Working with data was always my passion. Love to work with data, analyze those, and find patterns. Also, love to research. Always look for challenges to keep me growing.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo