# How to fix VLOOKUP #N/A with numbers stored as text

Make both sides the same type. When the table holds text IDs, turn the lookup number into text: =VLOOKUP(F1&"",A2:C7,2,FALSE).

**Excel:** `=VLOOKUP(F1&"",A2:C7,2,FALSE)` (Every current version)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C | D | E | F |
| --- | --- | --- | --- | --- | --- |
| Employee ID | Name | Dept |  | ID | 10433 |
| 10421 | Lena Park | Finance |  | Name |  |
| 10427 | Omar Ruiz | Sales |  |  |  |
| 10433 | Noor Haddad | IT |  |  |  |
| 10438 | Tom Becker | Sales |  |  |  |
| 10442 | Priya Shah | Marketing |  |  |  |
| 10450 | Ines Lopez | Finance |  |  |  |

Formula in F2: `=VLOOKUP(F1&"",A2:C7,2,FALSE)`

Result: Noor Haddad

## How it works

The IDs in column A are text, while `F1` holds the number 10433. A lookup never treats the number 10433 and the text "10433" as equal, so it returns #N/A. Joining an empty string, `F1&""`, turns the number into text, and the match works.

## Other ways

- `=XLOOKUP(F1,--A2:A7,B2:B7)`: Converts the ID column to numbers instead. Google Sheets needs ARRAYFORMULA around it.
- `=VLOOKUP(TEXT(F1,"0"),A2:C7,2,FALSE)`: TEXT does the same conversion with a format, which also handles leading zeros.

## Common mistakes

- The opposite case, numbers in the table and text in the lookup cell, needs the reverse fix: `=VLOOKUP(--F1,A2:C7,2,FALSE)`.
- Formatting a column as Number doesn't convert text already in it. Use Data, Text to Columns, Finish, or the warning icon's Convert to Number.
- Text numbers sit on the left of the cell by default, often with a small green triangle.

## Questions

**How do I check which side is text?**

`=ISTEXT(A4)` and `=ISTEXT(F1)`. The lookup works only when both give the same answer.

**Does XLOOKUP have the same problem?**

Yes. XLOOKUP, MATCH and INDEX/MATCH all treat 10433 and "10433" as different values.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/vlookup-na-numbers-stored-as-text