# XLOOKUP：用一个工号把另一张表的数据取过来，取不到时该显示什么

四个参数分别填什么、找不到和“找到但为空”为什么是两回事，以及拖到整列时哪些引用必须锁死

> Excel 函数 · 公式引用 · 数据整理 · 约 6 分钟 · 09 月 26 日

## 本篇要点

1. XLOOKUP 的结构是 XLOOKUP(要找什么, 到哪一列里找, 找到了取哪一列的值, 找不到时显示什么)，它先在第二列里定位行，再取第三列同一行的值。
2. 两个区域必须从同一行开始、长度一致；长度不同通常会直接报 #VALUE!，长度相同只差一个起始行时会安静地取到邻行的数据。
3. 第四参数只处理“完全没找到”的情况；匹配成功但返回列那一格是空的，XLOOKUP 会返回 0，需要额外处理，比如给结果接 &""，但要留意数字会被变成文本。
4. 往下拖时第一个参数保持相对引用，两个区域必须用 $ 绝对引用；不锁会让查找范围和返回范围一起下移错位。
5. 查找区域和返回区域起点不齐、两边一个是数字一个是文本、或者藏有看不见的空格，都会表现为 #N/A，需要先排查数据本身而不是重写公式。
6. XLOOKUP 只在 Excel 2021 和 Microsoft 365 及以上可用，老版本会报 #NAME?，可以用 INDEX 配 MATCH 实现同样效果。

---

上一篇里 IF 让单元格学会了自己判断。这篇要解决的是一类更常见的加班来源：算完一个数之前，先得去另一张表里把对应的信息找出来。

场景几乎每个用 Excel 的人都会碰到。明细表里只有客户的编号或工号，客户名称、所属部门、单价这些信息躺在另一张对照表里。以前的做法是两边各自排一次序，对齐了再复制粘贴，下次数据一变，全部重来。XLOOKUP 干的就是这件事：给一个“钥匙”值，去另一张表里找出这一行，然后把你要的那一列取过来。

## 四个位置分别填什么

结构是：

`=XLOOKUP(要找什么, 到哪一列里找, 找到了取哪一列的值, 找不到时显示什么)`

假设明细表 A 列是工号，花名册的 A 列也是工号、C 列是部门。在明细表 D2 里写：

`=XLOOKUP(A2, 花名册!$A$2:$A$100, 花名册!$C$2:$C$100, "查无此工号")`

读法是：拿 A2 这个工号，去花名册的 A2:A100 里逐行比对；找到之后，回到同一行，取 C 列里的值；一个都没找到，就显示“查无此工号”。

三个需要说清楚的地方：

**第二、第三个参数是“两列”，不是两个单元格。** XLOOKUP 先在第二个区域里找到位置（第几行），再到第三个区域里取同一行的值 [1]。所以两个区域必须从同一行开始、长度也一样。长度不一致时 Excel 通常会直接报 `#VALUE!`；真正难发现的是长度相同、只差一个起始行的情况——公式不报错，取回来的却是邻行的数据。

**它不关心想取的列在左边还是右边。** 老函数 VLOOKUP 只能指定“从这块区域的左边数第几列”，所以想取的列必须在查找列的右侧；XLOOKUP 直接给一整列，左右都行 [1]。这一条是很多人从 VLOOKUP 换过来的主要原因。

**默认要求两边完全相等。** 不额外指定的话，XLOOKUP 用的是精确匹配 [1]，工号必须一模一样才算找到。

## 取不到时显示什么

不写第四个参数，没找到就显示 `#N/A` [1]。

`#N/A` 是错误值，但它的含义很明确：“这张表里没有这一条”。满屏 `#N/A` 时，第一件该做的事不是重写公式，而是确认这些工号是不是真的不在对照表里——新旧员工交替、客户编号改版，都会造成这种情况。

写了第四个参数，没找到就换成你给的内容。但这里有个特别容易踩空的地方：

**第四个参数只管“没找到”，不管“找到了、但那一格是空的”。** 如果工号匹配成功，而返回列对应的那一格恰好没填内容，XLOOKUP 会显示 `0`，你写在第四参数里的话根本不会出现 [2]。这和 IF 里“空单元格被当成 0”是同一类麻烦：屏幕上那个 `0` 很容易被当成“数量是零”，混进后面的求和里，把合计算歪。

想让两种情况都显示空白，最常见的办法是给整个结果接一个空文本：`=XLOOKUP(...)&""` [3]。但要留意副作用：`&""` 会把数字结果也变成文本，看起来还是 123，实际已经是文本了，后续对这一列求和时 SUM 会把它跳过，合计就少算。所以要么等确认不需要再运算时再加，要么写成 `=IF(XLOOKUP(...)="","",XLOOKUP(...))`，把公式重复写一遍。XLOOKUP 本身没有直接参数能区分“没找到”和“找到但是空的”，这是它和 IFERROR 一样需要留心的边界。

## 拖到整列时，哪些引用要锁

公式要往下拖，第一行问 A2，第二行就得问 A3——第一个参数保持相对引用，让它跟着走。但后面两个区域必须钉死：

`=XLOOKUP(A2, 花名册!$A$2:$A$100, 花名册!$C$2:$C$100, "查无此工号")`

不锁会怎样？拖到下一行时，两个区域会跟着整体下移一格，变成 `花名册!A3:A101` 对 `花名册!C3:C101`。查找范围和返回范围错开了，取回来的是邻居那行的数据。这个错误最难发现的地方在于它**不报错**：结果看着都有值，只有拿着原始资料逐个核对才看得出来，而且最后一行还可能莫名冒出 `#N/A`。

跨工作表引用还要带上工作表名和感叹号，写成 `花名册!$A$2:$A$100` 这种形式，否则 Excel 会去当前表里找。

填好第一行以后，不必一行行拖，双击单元格右下角的小方块就能自动填到底。填完至少抽查三处：第一行、最后一行，以及你刻意留的一条“应该找不到”的行。

## 明明看到了，为什么返回 #N/A

很多 `#N/A` 不是公式写错，而是“看起来一样，在 Excel 眼里不一样”[4]：

- **一个是数字、一个是文本。** 明细表里的工号是导入来的文本“1001”，花名册里存的是数字 1001，两者不相等。用 `ISNUMBER` 和 `ISTEXT` 分别看两边就能确认，必要时用 VALUE 把一侧转成数字。
- **藏着看不见的空格。** 名字或编号末尾多一个空格、从网页复制来的不间断空格，肉眼完全看不出。可以用 TRIM、CLEAN 清洗，或比较两边的 LEN 长度。
- **区域起点不齐。** 上面说的错位，表现常常是“有些行对、有些行不对”，而不是整列都错。

批量清洗留到后面专门讲。现在能用的判断规则是：如果某一项确实存在、两边看上去字字相同却报 `#N/A`，先怀疑类型和空格，而不是重写公式。

## 你的 Excel 里有这个函数吗

XLOOKUP 从 Excel 2021 和 Microsoft 365 开始提供，Excel 2019 和 2016 里没有。老版本打开带这个公式的文件会显示 `#NAME?`，意思是“不认识这个名字”[5]。碰到 `#NAME?`，先去“文件 → 账户”看版本；文件如果存成了老式的 `.xls` 格式，也可能报这个错 [5]。

没有 XLOOKUP 也能做同样的事，用 INDEX 配 MATCH 写：

`=IFERROR(INDEX(花名册!$C$2:$C$100, MATCH(A2, 花名册!$A$2:$A$100, 0)), "查无此工号")`

MATCH 找出 A2 在花名册 A2:A100 里排第几行，INDEX 按这个行号去 C2:C100 取同一行的值。思路和 XLOOKUP 完全一样，只是拆成两步、括号多一点。

## 写完检查四件事

1. 第一个参数是相对引用，两个区域都用 `$` 锁死，跨表写全了工作表名；
2. 查找区域和返回区域的起点、长度一致；
3. 想过“匹配成功但那一格是空的”这种情况，知道它会返回 0；
4. 第一行、最后一行、故意找不到的那一行，各看一遍。

最后数一下 `#N/A` 有几条，逐条确认是“记录真的不存在”，还是类型、空格造成的不相等。

## 术语表

- XLOOKUP：按一个钥匙值去某一列里逐行查找，找到后从另一列同一行取值并返回的函数。
- lookup_value / lookup_array / return_array：要找的值、去哪个区域找、找到了从哪个区域取值，后两个区域必须逐行对齐。
- if_not_found：XLOOKUP 的第四个参数，只在完全没有匹配项时生效；匹配成功但结果格为空会返回 0，不归它管。
- 精确匹配（match_mode 0）：不额外指定时的默认行为，要求两边完全相等，数字和文本不算相等。
- 区域错位：查找区域与返回区域的起点或长度不一致，导致取到相邻行的值，公式不报错但结果全错。

## 来源

1. [微软官方 XLOOKUP 文档 — 参数含义、默认精确匹配、未提供 if_not_found 时返回 #N/A、适用版本](https://support.microsoft.com/en-us/office/xlookup-function-b7fd680e-6d10-43e6-84f9-88eae8bf5929)
2. [Microsoft Q&A：匹配成功但返回格为空时返回 0，if_not_found 不生效](https://learn.microsoft.com/en-us/answers/questions/5107699/xlookup-if-not-found)
3. [Super User：用 &"" 让空结果不显示为 0](https://superuser.com/questions/1551767/xlookup-result-for-blank-values-is-0)
4. [Excel University：文本与数字类型不一致导致查不到，及用 VALUE 转换的写法](https://www.excel-university.com/avoid-xlookup-errors/)
5. [Microsoft Q&A：#NAME? 与 Excel 版本、文件格式的关系](https://learn.microsoft.com/en-us/answers/questions/5727915/error-name-using-the-xlookup-formula)

---

原文：https://pangzhengboyin.com/articles/xlookup-cross-sheet-lookup-basics-5be14f3e

> **庞征博引** · 想学的，慢慢都会
>
> 庞征博引是把想学的东西写成连载的 AI 学习工具。说出想学什么，它会先了解你的基础，再把主题写成一篇篇 5–10 分钟能读完的文章；边读边问，接下来学什么跟着你走。这篇就是这样写出来的。
>
> 开始你自己的连载 → https://pangzhengboyin.com