How to look up codes that have leading zeros in Excel

Q: How do I do it when codes have different lengths?

Compare as numbers on both sides: =XLOOKUP(--F1,--A2:A8,B2:B8).

Formula (Excel and Google Sheets)

=XLOOKUP(TEXT(F1,"00000"),A2:A8,B2:B8,"Not found")

Pad the typed number back to the code's length with TEXT: =XLOOKUP(TEXT(F1,"00000"),A2:A8,B2:B8,"Not found").

Result on the example: Hydraulic hose 1/2 in 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

Typing 00451 into a normal cell stores the number 451 and drops the zeros, so it never equals the text "00451". TEXT(F1,"00000") formats the number with five digits, zeros included, and returns text, which matches the code in the list.

=XLOOKUP(TEXT(F1,"00000"),A2:A8,B2:B8,"Not found")

Result"Hydraulic hose 1/2 in"

F1451
The value in F1.
TEXT(F1,"00000")"00451"
TEXT: Formats a number or date as text, e.g. "mmm" or "$#,##0".
- valueF1451
- format_text"00000"
A2:A87 rows × 1 col: "00118", "00203", "00451", "01020", …
A range: 7 cells in one column.
B2:B87 rows × 1 col: "Ball bearing 6202", "V-belt A42", "Hydraulic hose 1/2 in", "Pillow block 1 in", …
A range: 7 cells in one column.
XLOOKUP(TEXT(F1,"00000"),A2:A8,B2:B8,"Not found")"Hydraulic hose 1/2 in"
XLOOKUP: Searches one range and returns the matching item from another.
- lookup_valueTEXT(F1,"00000")"00451"
- lookup_arrayA2:A87 rows × 1 col: "00118", "00203", "00451", "01020", …
- return_arrayB2:B87 rows × 1 col: "Ball bearing 6202", "V-belt A42", "Hydraulic hose 1/2 in", "Pillow block 1 in", …
- if_not_found"Not found"

In Google Sheets

The same formula works in Google Sheets: =XLOOKUP(TEXT(F1,"00000"),A2:A8,B2:B8,"Not found"). We ran it in Google Sheets mode on the same data and it returns Hydraulic hose 1/2 in.

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:C8,2,FALSE)	Hydraulic hose 1/2 in	The same padding with VLOOKUP, for every version.
=XLOOKUP(F1,--A2:A8,B2:B8)	Hydraulic hose 1/2 in	Converts the codes to numbers instead, so it works whatever their length. Don't use it if two codes differ only in their zeros.

Common mistakes

The number of zeros in the TEXT pattern must match the code length. "0000" would give "0451".
Format the input cell as Text before typing, or start the entry with an apostrophe ('00451), to keep the zeros.
Joining "" (F1&"") turns 451 into "451", which still has no leading zeros.

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.

Look up codes that have leading zeros

The item codes are five-character text with leading zeros, but the code in F1 was typed as a number, 451. In F2, return the description for it.

Questions

How do I do it when codes have different lengths?

Compare as numbers on both sides: =XLOOKUP(--F1,--A2:A8,B2:B8).

Does Google Sheets keep leading zeros?

Only if the cell is formatted as plain text. The same TEXT fix works there.

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.