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

## Problems with the Text and Number Formats in VLOOKUP

If your lookup value and the column values are in a different format, it will cause an error.

Consider this dataset.

We used the following VLOOKUP formula to find the Movie name:

`=VLOOKUP(G4,\$B\$4:\$D\$12,2,FALSE)`

To get the Actor name, we used:

`=VLOOKUP(G4,\$B\$4:\$D\$12,3,FALSE)`

Our lookup value was in the number format and our lookup column has the text format. That’s why it couldn’t give us the result we wanted. We have to convert the number to get the actual result.

## How to Convert Number to Text for VLOOKUP in Excel: 2 Ways

### Method 1 – Use the TEXT Function to Convert a Number to Text for VLOOKUP

The Generic Formula:

=VLOOKUP(TEXT(cell,0),table_array,column_index_number,FALSE)

• To get the Movie name, use:

`=VLOOKUP(TEXT(G4,0),\$B\$4:\$D\$12,2,FALSE)`

• To get the Actor name, use the following formula:

`=VLOOKUP(TEXT(G4,0),\$B\$4:\$D\$12,3,FALSE)`

### Method 2 – Concatenate with an Empty String to Convert a Number to Text

The Generic Formula:

=VLOOKUP(lookup_value&””,table_array,column_index_number,FALSE)

By concatenating (ampersand symbol for string values) with a blank string (nothing in double quotes), we can convert the lookup value from a number to text.

• To get the Movie name:

`=VLOOKUP(G4&"",\$B\$4:\$D\$12,2,FALSE)`

• To get the Actor name, use the following formula:

`=VLOOKUP(G4&"",\$B\$4:\$D\$12,3,FALSE)`

Important Tip

If you are not sure whether the lookup value is in the numbers or text format, use the IFERROR function as in the following formula:

`=IFERROR(VLOOKUP(G4,\$B\$4:\$D\$12,2,FALSE),VLOOKUP(G4&"",\$B\$4:\$D\$12,2,FALSE))`

The formula will 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. If this also fails, it will throw the #N/A error.

## Convert Text to Number for the VLOOKUP Function in Excel

Consider the following dataset where the first column in the number format but the lookup value in the VLOOKUP function is in the text format.

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)

• To get the Movie name:

`=VLOOKUP(VALUE(G4),\$B\$4:\$D\$12,2,FALSE)`

• Get the Actor name by using the following formula:

`=VLOOKUP(VALUE(G4),\$B\$4:\$D\$12,3,FALSE)`

## Things to Remember

✎ If you are not sure about the formats, wrap the VLOOKUP in the IFERROR function as we discussed earlier.

✎ Turn on error checking to find the numbers stored as text.

Download the Practice Workbook

## Related Articles

<< Go Back to Excel Convert Number to Text | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
A.N.M. Mohaimen Shanto

A.N.M. Mohaimen Shanto, a B.Sc. in Computer Science and Engineering from Daffodil International University, boasts two years of experience as a Project Manager at Exceldemy. He authored 90+ articles and led teams as a Team Leader, meticulously reviewing over a thousand articles. Currently, he focuses on enhancing article quality. His passion lies in Excel VBA, Data Science, and SEO, where he enjoys simplifying complex ideas to facilitate learning and growth. His journey mirrors Exceldemy's dedication to excellence and... Read Full Bio

2 Comments
1. 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

Advanced Excel Exercises with Solutions PDF