Formula Errors

How to Fix VLOOKUP Returning #N/A for IDs with Leading Zeros

Aamir Naveed Akram Sep 30, 2026 869 views

Question details

The user needs to fix a VLOOKUP formula returning an #N/A error when attempting an exact match on ID numbers that contain leading zeros.

How to Fix VLOOKUP Returning #N/A for IDs with Leading Zeros

Product: Spreadsheet
Device & OS: not provided
Scenario: Looking up matching records across datasets containing unique identifiers (such as medical records or REDCap IDs) where leading zeros are present.
Observed behavior: VLOOKUP returns an #N/A error with FALSE (exact match) despite the IDs appearing identical, due to mismatched data types or inconsistent leading zero formatting.

Before you start

Ensure you are working on a sanitized copy of your dataset to protect sensitive data, and verify that the target column actually contains the lookup value you are searching for.

Solution 1Recommended

Standardize Both Lookup Columns to Text Format

Converting both the lookup value and the lookup array to a Text format ensures that leading zeros are preserved and data types match perfectly.

The most common cause for an #N/A error in VLOOKUP is a mismatch in data types. If one dataset treats the ID as a number (dropping the leading zero) and the other treats it as text (keeping the zero), Excel and WPS Spreadsheet will view them as completely different values.

Select the lookup value column

Highlight the entire column containing the IDs you want to look up.

Open the Text to Columns wizard

Navigate to the 'Data' tab on the ribbon and click on 'Text to Columns'.

Navigate to the final step

Select 'Delimited', click 'Next' twice to bypass the delimiter options, and arrive at the 'Column data format' step.

Apply Text format

Select the 'Text' radio button and click 'Finish'. Repeat this exact process for the column containing the lookup array.

Standardize Both Lookup Columns to Text Format

Data Type Alignment: By forcing both columns into a strict Text format via the Text to Columns tool, you strip underlying numeric formatting conflicts that cause the #N/A error.

Solution 2

Use the TEXT Function Inside the Formula

If one column is stored as a number without leading zeros, you can pad the lookup value with zeros dynamically using the TEXT function.

Solution 3

Clean Data Using TRIM and CLEAN

Remove hidden spaces or non-printable characters that often accompany data exported from external systems.

Resolve Formula Errors Faster

Easily Manage Complex Data with WPS Spreadsheet

WPS Spreadsheet provides robust data formatting tools and intuitive error checking to help you seamlessly handle complex datasets, fix #N/A formula errors, and keep your leading zeros intact.

1. Open WPS Spreadsheet: Launch WPS Office and open your dataset containing the VLOOKUP errors.
2. Format Cells as Text: Highlight your ID columns, right-click, select 'Format Cells', and choose 'Text' to preserve leading zeros.
3. Insert Formula: Type =VLOOKUP() and use the intuitive formula helper to select your lookup value, table array, and exact match criteria.
4. Evaluate Errors: If an error persists, click the warning triangle next to the cell to use WPS Spreadsheet's built-in error tracing tool.

100% compatible with Microsoft Excel file formats (.xlsx) and functionsBuilt-in 'Text to Columns' and data cleaning features for easy formattingIntelligent error detection for formulas like VLOOKUP and XLOOKUPLightweight, fast, and completely free to use for daily tasks

microsoft office alternative - wps office

Frequently Asked Questions

Why does VLOOKUP work for some IDs but return #N/A for others?

This happens when your dataset has mixed data types. IDs without leading zeros might be stored as numbers, resulting in successful matches, while IDs with leading zeros might be stored as text (or vice versa). VLOOKUP requires exact data type matching.

How can I add missing leading zeros back to my ID numbers?

You can use the TEXT function (e.g., =TEXT(A2, "000000")) to pad numbers to a specific length, or use the custom number format '000000' in the Format Cells menu if you only need them to display visually.

Will using TRUE instead of FALSE fix the #N/A error?

No, changing FALSE (exact match) to TRUE (approximate match) is dangerous for IDs. It will stop the #N/A error but will likely return incorrect data by grabbing the closest match instead of the exact medical record or ID you need.

Can I use XLOOKUP instead of VLOOKUP to solve this?

XLOOKUP is a powerful alternative, but it still requires matching data types. If one column is text and the other is numeric, XLOOKUP will also fail to find a match unless you standardise the formats or wrap the lookup value in a TEXT or VALUE function.

Excel & Data Problems