Match data
VLOOKUP Not Working? Why You Get #N/A — and How to See the Real Reason
A trailing space, hidden character, text-versus-number mismatch, or missing ID can all cause #N/A. Diagnose the real reason before merging sheets.
TidyCell
Match & Merge
Bring selected fields across by a shared ID. Review missing and duplicate keys before keeping the result.
Open Match & MergeYou can see the customer ID in the other sheet, yet VLOOKUP returns #N/A. That one error can mean a missing record, a stray space, an invisible character, or a number stored as text. Find the cause before hiding the error with IFERROR.
Check the formula first
An exact VLOOKUP needs FALSE as its fourth argument: =VLOOKUP(A2,Sheet2!$A$2:$D$500,4,FALSE). Without it, Excel may perform an approximate lookup against an unsorted list. Also check that the lookup key is in the first column of the selected range and that the range stays fixed when the formula is copied down.
Why a correct formula still returns #N/A
| What you see | What to check | Typical fix |
|---|---|---|
AC-9036 versus AC-9036 | Leading or trailing spaces | Use TRIM on both lists |
| Two IDs that look identical | Non-breaking or zero-width characters | Inspect the character, then use a targeted SUBSTITUTE |
1001 versus text 1001 | Number and text storage | Convert deliberately; preserve zero-padded IDs |
| No matching row | The ID is genuinely absent | Keep the missing record visible for review |
TRIM removes ordinary surrounding spaces. A non-breaking space copied from a website or PDF may need =TRIM(SUBSTITUTE(A2,CHAR(160)," ")). Number conversion needs care: 00123 and 123 can be different account IDs. Forcing both to a number can create a wrong match.
Why IFERROR is a poor first fix
=IFERROR(VLOOKUP(...),"") makes the cell look clean, but it combines genuine missing customers with repairable data problems. Once both appear blank, the sheet gives you no way to tell which rows need attention. Use it only after you have checked the misses.
Match sheets without a lookup formula
TidyCell Match & Merge starts with the job: choose the destination sheet, choose another tab or file, confirm the matching columns, and select the fields to add. It shows matched rows, missing IDs, blank IDs, and duplicate keys before you confirm the token cost. The spreadsheets are read on your device.
The output appends the selected fields to the supported destination workbook. Your input files remain untouched. Rows without a match stay in the output with blank new cells. Surrounding-space and letter-case options affect matching only; they do not rewrite the IDs. Duplicate IDs in the source require an explicit first-or-last rule if they affect destination rows.
Common questions
Does XLOOKUP fix the same problem?
XLOOKUP defaults to exact matching and can look in either direction. It does not remove spaces or hidden characters, or resolve a number-versus-text mismatch in the underlying data.
Do capital letters cause VLOOKUP to fail?
Ordinary VLOOKUP and XLOOKUP are case-insensitive. Other matching systems can differ. TidyCell offers an explicit option to ignore letter case so the rule is visible.
Can the reference list be in a second file?
Yes. Match & Merge accepts another sheet in the first workbook or a separate .xlsx or .csv file.
What if there are duplicate customer IDs?
Read why VLOOKUP chooses the first duplicate before deciding whether first or last has a real meaning in your source list.