# How to look up IDs that are formatted differently in Excel

Strip the characters that differ from both sides inside the lookup: `=XLOOKUP(SUBSTITUTE(F1,"-",""),SUBSTITUTE(A2:A7,"-",""),C2:C7,"Not found")`.

**Excel:** `=XLOOKUP(SUBSTITUTE(F1,"-",""),SUBSTITUTE(A2:A7,"-",""),C2:C7,"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 |
| --- | --- | --- | --- | --- | --- |
| Part number | Description | Price |  | Scanned code | TR2207B |
| TR-1104-A | Brake pad set | 42.5 |  | Price |  |
| TR-1388-C | Rotor 11 in | 68.9 |  |  |  |
| TR-2207-B | Caliper bracket | 23.75 |  |  |  |
| TR-2290-A | Wheel bearing | 54 |  |  |  |
| TR-3015-D | Tie rod end | 31.2 |  |  |  |
| TR-3122-B | Sway bar link | 19.95 |  |  |  |

Formula in F2: `=XLOOKUP(SUBSTITUTE(F1,"-",""),SUBSTITUTE(A2:A7,"-",""),C2:C7,"Not found")`

Result: $23.75

## How it works

SUBSTITUTE removes the dashes, so TR-2207-B becomes TR2207B. Doing it to the whole lookup range inside XLOOKUP compares cleaned values on both sides, without a helper column or changing the source data.

## Other ways

- `=INDEX(C2:C7,MATCH(SUBSTITUTE(F1,"-",""),SUBSTITUTE(A2:A7,"-",""),0))`: For Excel 2019 and earlier (Ctrl+Shift+Enter in 2016 and older).

## Common mistakes

- Clean both sides. Cleaning only the lookup value still fails against the dashed list.
- SUBSTITUTE returns text. If one side is a number, like 22070, convert both to text with `&""` before comparing.

## Questions

**How do I remove dashes and spaces?**

Nest SUBSTITUTE: `SUBSTITUTE(SUBSTITUTE(A2:A7,"-","")," ","")`. In Google Sheets, `REGEXREPLACE(A2:A7,"[- ]","")` removes several characters at once.

**How do I match IDs with and without leading zeros?**

Convert both to numbers with `--`, or pad both with TEXT, for example `TEXT(F1,"000000")`.

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-ids-formatted-differently