How to fix VLOOKUP #N/A when IDs have leading zeros
Formula (Excel and Google Sheets)
=XLOOKUP(TEXT(F1,"00000"),A2:A7,C2:C7)Pad the lookup value into the same text format as the table: =XLOOKUP(TEXT(F1,"00000"),A2:A7,C2:C7).
- Result on the example
- A-07 in
F2 - Works in
- Excel 2021, 2024 and Microsoft 365
- Google Sheets
- Same formula
See it work
The formula is already in F2. Change any input and the result updates, or edit the formula to experiment.
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.
=XLOOKUP(TEXT(F1,"00000"),A2:A7,C2:C7)Result"A-07"
F1412The value in F1.
TEXT(F1,"00000")"00412"TEXT: Formats a number or date as text, e.g. "mmm" or "$#,##0".- value
F1412 - format_text
"00000"
- value
A2:A76 rows × 1 col: "00385", "00412", "01120", "02005", …A range: 6 cells in one column.
C2:C76 rows × 1 col: "A-03", "A-07", "B-02", "B-11", …A range: 6 cells in one column.
XLOOKUP(TEXT(F1,"00000"),A2:A7,C2:C7)"A-07"XLOOKUP: Searches one range and returns the matching item from another.- lookup_value
TEXT(F1,"00000")"00412" - lookup_array
A2:A76 rows × 1 col: "00385", "00412", "01120", "02005", … - return_array
C2:C76 rows × 1 col: "A-03", "A-07", "B-02", "B-11", …
- lookup_value
In Google Sheets
The same formula works in Google Sheets: =XLOOKUP(TEXT(F1,"00000"),A2:A7,C2:C7). We ran it in Google Sheets mode on the same data and it returns A-07.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =VLOOKUP(TEXT(F1,"00000"),A2:C7,3,FALSE) | A-07 | The same fix with VLOOKUP. |
| =XLOOKUP(F1,--A2:A7,C2:C7) | A-07 | 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.
Try it yourself
Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.
Fix VLOOKUP #N/A when IDs have leading zeros
F2, return the bin for the part number typed in F1. The table stores part numbers as five-character text with leading zeros.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.
Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.