# How to fix VLOOKUP #N/A when IDs have leading zeros

Pad the lookup value into the same text format as the table: =XLOOKUP(TEXT(F1,"00000"),A2:A7,C2:C7).

**Excel:** `=XLOOKUP(TEXT(F1,"00000"),A2:A7,C2:C7)` (Excel 2021, 2024 and Microsoft 365)

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

## Example

Sample data (row 1 is the header):

| A | B | C | D | E | F |
| --- | --- | --- | --- | --- | --- |
| Part no. | Description | Bin |  | Part no. | 412 |
| 00385 | Hex bolt M6 | A-03 |  | Bin |  |
| 00412 | Washer 6mm | A-07 |  |  |  |
| 01120 | Lock nut M8 | B-02 |  |  |  |
| 02005 | Spring pin | B-11 |  |  |  |
| 10230 | Hinge 2in | C-04 |  |  |  |
| 00098 | Cable tie | A-01 |  |  |  |

Formula in F2: `=XLOOKUP(TEXT(F1,"00000"),A2:A7,C2:C7)`

Result: A-07

## How it works

The part numbers in column A are text with leading zeros, "00412". The 412 typed in `F1` is a number without them, so nothing matches. `TEXT(F1,"00000")` turns it into the five-character text "00412", the same as the table.

## Other ways

- `=VLOOKUP(TEXT(F1,"00000"),A2:C7,3,FALSE)`: The same fix with VLOOKUP.
- `=XLOOKUP(F1,--A2:A7,C2:C7)`: Converts the table side to numbers instead, which works whatever the lengths. Google Sheets needs ARRAYFORMULA around it.

## Common mistakes

- The pattern must match the stored length: "00000" for five characters. For IDs of mixed lengths, convert the table side instead.
- Typing 00412 into a cell stores 412. Format the input cell as Text first, or start with an apostrophe: '00412.

## Questions

**How do I keep leading zeros when importing a CSV?**

Import the column as Text (Data, From Text/CSV, Transform Data), or the zeros are dropped on the way in.

**How do I add the zeros back to a whole column?**

`=TEXT(A2,"00000")` in a helper column, then Paste 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-leading-zeros