When VLOOKUP returns nothing but N/A

Added 2026-10-29

When VLOOKUP returns nothing but N/A — Chinese original prompt

你是天天和表格打交道的运营。这条公式 [粘贴公式原文] 在 [Excel/WPS 版本] 里对 [查找列] 和 [数据源区域] 做匹配，结果全是 #N/A，但肉眼看着两边一样。已知情况：[是否有前后空格、是否是文本格式的数字、数据源有没有合并单元格、有没有重复值、查找值是否超过 15 位]。请：1）按最可能到最不可能排出排查顺序，每步写清怎么验证；2）针对命中的原因给改法，说明用 TRIM、CLEAN、分列、VALUE 还是统一成文本；3）判断要不要换成 XLOOKUP 或 INDEX+MATCH，给出理由；4）给三条防复发规则，写进这张表的填写约定；5）指出哪些 #N/A 其实是正常无匹配，不该硬修。验收：能定位到具体哪一处不一致，并给出可复制的修正公式。

Site original

Chinese original

Replace these placeholders 4

Swap the bracketed parts for your own details before sending.

[粘贴公式原文]
[Excel/WPS 版本]
[查找列]
[数据源区域]

How to use

先看是不是文本数字，别一上来就重装表格。

Try tweaking it

Ask for three columns: conclusion, owner, deadline — ready to paste into a table.
Let it organise only your notes; do not add decisions that were never made.

What you should get

Task list with owner, deadline and blocker
Escalations that need a decision, listed separately

Common mistakes

Feeding raw notes and letting it fill in decisions that were never made.
Getting owners without deadlines, so no task can be created.

How to check the output

Every item has an owner and a date — ready to become a task.
It does not record decisions that were never made.

Where it works

Good for: turning verbal discussion into owner, deadline and blocker.
Watch out: let it organise only the notes you supply — never let it infer decisions that were not made.