Excel: How to Use VLOOKUP with Numbers Stored as Text

You can use the following syntax to use a VLOOKUP function in Excel to look up a number that is stored as a text in a range where the numbers are stored as ordinary numbers:

=VLOOKUP(VALUE(E1), A2:B11, 2, FALSE)

This particular formula looks up the number in cell E1 (in which this value is saved as text) in the range A2:B11 and returns the corresponding value in the second column of the range.

Note that this formula uses the VALUE function to convert a text string that represents a number into a numeric value.

The following example shows how to use this syntax in practice.

Example: How to Use VLOOKUP with Numbers Stored as Text

Suppose we have the following dataset in Excel that contains information about the sales made by various employees at some company:

Now suppose that we would like to look up the employee ID 3490 and return the corresponding sales value.

However, suppose this ID value is stored as Text in Excel.

Suppose we attempt to use the following VLOOKUP function to look up this value:

=VLOOKUP(E1, A2:B11, 2, FALSE)

The following screenshot shows how to use this formula in practice:

We receive #N/A as a result because the value in cell E1 is stored as Text.

To get around this issue, we must use the VALUE function within the VLOOKUP function:

=VLOOKUP(VALUE(E1), A2:B11, 2, FALSE)

The following screenshot shows how to use this formula in practice:

Excel VLOOKUP number stored as text

The formula correctly returns a value of 22, which matches the sales value associated with employee ID 3490.

If you have the opposite scenario where the lookup value is stored as a number but the numbers in the range are stored as text, you can use the following formula instead: