# How to look up codes that have leading zeros in Excel

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

**Excel:** `=XLOOKUP(TEXT(F1,"00000"),A2:A8,B2:B8,"Not found")` (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 |
| --- | --- | --- | --- | --- | --- |
| Item code | Description | Bin |  | Code typed | 451 |
| 00118 | Ball bearing 6202 | A1 |  | Description |  |
| 00203 | V-belt A42 | A3 |  |  |  |
| 00451 | Hydraulic hose 1/2 in | B2 |  |  |  |
| 01020 | Pillow block 1 in | B5 |  |  |  |
| 01377 | Roller chain #40 | C1 |  |  |  |
| 02210 | Gear motor 0.5 hp | C4 |  |  |  |
| 04085 | Shaft coupling | D2 |  |  |  |

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

Result: Hydraulic hose 1/2 in

## 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.

## Other ways

- `=VLOOKUP(TEXT(F1,"00000"),A2:C8,2,FALSE)`: The same padding with VLOOKUP, for every version.
- `=XLOOKUP(F1,--A2:A8,B2:B8)`: 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.

## 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.

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/lookup-codes-with-leading-zeros