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.

**Table of Contents**hide

## 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