A numeric 1001 and text "1001" may not match in an exact lookup. Align the type of the lookup value and the first column of your lookup table.
Excel 2016 and later / XLOOKUP availability varies
Inspect the type, not just the appearance
Test the lookup cell and the matching key in the table. ISNUMBER identifies numeric values; ISTEXT identifies text.
=ISNUMBER(A2)
=ISTEXT(A2)| Example | Type | Why it matters |
|---|---|---|
| 1001 | Number | Compare with numeric keys |
| '1001 | Text | The apostrophe forces text on entry |
| 00123 | Keep as text | Leading zeros can be meaningful |
Check exact match and the table range
This formula searches E2:E10 for A2 and returns the corresponding value from F2:F10. FALSE requests an exact match. The key must be in the first column of the selected table.
=VLOOKUP(A2,$E$2:$F$10,2,FALSE)A key outside the range cannot match. Duplicate keys return the first match, so confirm that this is appropriate for your data.
Convert genuine numeric keys in a helper column
If the key is genuinely numeric, create a helper column and inspect the converted results before replacing anything. Align both sides of the lookup.
=VALUE(TRIM(A2))Changing a cell’s display format to Number does not necessarily convert the stored text. If VALUE fails, check for extra symbols, unusual spaces, or full-width characters.
Preserve identifiers with leading zeros
VALUE converts 00123 to 123. Keep identifiers as text when those zeros matter. Only if your ID specification is exactly five digits, you can normalize a numeric value with:
=TEXT(A2,"00000")Download the example
Example for this guideFree download · .csvUTF-8 CSV data. Import with Data → From Text/CSV. Set identifier columns to Text when leading zeros matter. Charts and formulas are added using the steps in this guide.
Further reading
Microsoft Support — #N/A ↗This is a newly written guide to the topic referenced by the legacy URL, not a reproduction of the former article. Screens and features can differ by Excel version.