VLOOKUP With Numbers Stored as Text

VLOOKUP returns #N/A when you look up a number stored as text in a column of real numbers, or the other way round. The cells look identical, but VLOOKUP compares the type as well as the digits. This guide shows how to find which side is text and how to fix it, either inside the formula with VALUE or &"", or once and for all in the data.

Why VLOOKUP fails with numbers stored as text#

VLOOKUP with an exact match only finds values of the same type, so the number 1003 never matches the text "1003". A number stored as text is a cell that shows digits but holds text. It often comes from a system export, a CSV file or a value typed with a leading apostrophe, like '1003.

Here is a small product list. The IDs in column A are real numbers. The ID typed in E2 was pasted from an email and is text.

This formula looks right, but it returns #N/A:

=VLOOKUP(E2,A2:C6,3,FALSE)

The fourth argument, FALSE, asks for an exact match. Excel searches column A for the text "1003", finds only numbers, and gives up. The VLOOKUP function guide explains each argument if you need a refresher.

How to tell which side is text#

Test each cell with ISNUMBER before you change anything. It returns TRUE for a real number and FALSE for text.

So the lookup value is the text side. You can also look for these visual clues:

• Alignment. By default, numbers sit on the right of a cell and text sits on the left.
• A green triangle in the top-left corner of the cell. Excel flags numbers stored as text this way, unless the check is turned off.
• The status bar. Select the column. If it shows a Count but no Sum, the cells are text.

Formatting a cell as Number does not fix it. The format changes how a value is shown, not what it is. The article on numbers stored as text in Excel covers all the ways to spot them.

Fix 1: The lookup value is text and the table has numbers#

Wrap the lookup value in VALUE, which turns text that looks like a number into a real number:

=VLOOKUP(VALUE(E2),A2:C6,3,FALSE)

VALUE(E2) turns the text "1003" into the number 1003, so VLOOKUP finds row 4 and returns 45, the price of the Lamp. The VALUE function guide shows more examples.

Two shorter versions do the same job, because any maths on text digits makes Excel convert them:

=VLOOKUP(E2*1,A2:C6,3,FALSE)
=VLOOKUP(--E2,A2:C6,3,FALSE)

The double minus, --, turns the value negative and back again. All three return 45. VALUE is the easiest for someone else to read.

If the text also has a stray space, such as "1003 ", VALUE usually still works. If it returns #VALUE!, the cell holds something that isn't a digit, often a non-breaking space from a web page. Clean it first with VALUE(TRIM(SUBSTITUTE(E2,CHAR(160)," "))).

Fix 2: The lookup value is a number and the table has text#

Turn the number into text by joining it to an empty string with &"":

=VLOOKUP(E2&"",A2:C6,3,FALSE)

Say column A was imported as text and E2 holds the real number 1003. E2&"" gives the text "1003", which matches the text in column A, and the result is again 45.

When the IDs have leading zeros#

Codes like 00123 must stay as text, or Excel drops the zeros. If the table holds "00123" and the lookup cell holds the number 123, E2&"" gives "123" and still fails. Use TEXT to add the zeros back:

=VLOOKUP(TEXT(E2,"00000"),A2:C6,3,FALSE)

The format "00000" pads the number to five digits, so 123 becomes "00123". Match the number of zeros to the length of your codes.

Fix 3: The column mixes numbers and text#

When some IDs are numbers and some are text, try both versions with IFERROR:

=IFERROR(VLOOKUP(E2,A2:C6,3,FALSE),VLOOKUP(E2&"",A2:C6,3,FALSE))

The first VLOOKUP tries the value as it is. If that returns an error, the second tries it as text. If E2 itself might be text, use VALUE(E2) in one lookup and E2&"" in the other. This is a patch, though. A mixed column will cause trouble in SUM, COUNTIF and sorting too, so plan to clean it.

How to convert the whole column instead#

Fixing the data is better than fixing every formula. Convert the ID column to real numbers once, and every lookup works with a plain =VLOOKUP(E2,A2:C6,3,FALSE).

1. Convert to Number. Select the cells with the green triangle, click the warning icon and choose Convert to Number.
2. Text to Columns. Select the column, go to Data > Text to Columns and click Finish. Excel re-reads each cell as a number.
3. Paste Special Multiply. Type 1 in a spare cell and copy it (Windows: Ctrl+C; Mac: ⌘C). Select the text numbers, open Paste Special (Windows: Ctrl+Alt+V; Mac: ⌃⌘V), choose Multiply and click OK.
4. A helper column. In a new column, type =VALUE(A2), fill down, then paste the results back as values.

Text dates cause the same kind of mismatch when you look up by date. See how to convert text to dates for that case. If your lookup returns a value but the wrong one, the cause is different; the guide to VLOOKUP returning the wrong value walks through it.

Does XLOOKUP have the same problem?#

Yes. XLOOKUP, MATCH and XMATCH also treat 1003 and "1003" as different values, so the same fixes apply:

=XLOOKUP(VALUE(E2),A2:A6,C2:C6)

This returns 45. XLOOKUP needs Excel 2021 or Microsoft 365. Text numbers are only one reason a lookup fails. The checklist of why VLOOKUP is not working covers the other seven, and the article on the VLOOKUP column index number helps when the formula returns a value from the wrong column.

Practise it#

The fastest way to remember these fixes is to meet the error and fix it yourself. Practise fixing lookup errors in a live spreadsheet: you'll repair #N/A results caused by text numbers and spaces, and the sheet checks each answer instantly. Then practise TEXT and VALUE to convert numbers to text and back with confidence.