🤖 AI 跟我学 新手入门

Excel VLOOKUP 不会用？让 AI 帮你写公式

Excel VLOOKUP AI 教程：用大白话描述跨表查找需求，AI 直接给出 VLOOKUP 或 XLOOKUP 公式，含 5 个真实场景示例、3 个常见报错救援和升级到 XLOOKUP 的完整对比

发布 2026/04/28 📎 参考官方文档

你打开 Excel 想做两张表关联——表 A 是订单号 + 商品 ID，表 B 是商品 ID + 商品名 + 单价，要把商品名和单价填到表 A——脑子里第一反应是「VLOOKUP」，然后开始翻函数手册查参数顺序：lookup_value、table_array、col_index_num、range_lookup……第 3 个参数到底数到第几列？让 AI 写这种 Excel VLOOKUP 公式，是最省事的解法。

其实 2026 年这种活儿已经不用人肉算列号了。把需求用大白话告诉 AI，AI 直接给出能粘进单元格的 VLOOKUP（或更新的 XLOOKUP）公式，附带逐行解释。

这篇文章给你一个万能 prompt 模板，5 个真实场景示例，再讲清楚什么时候用 VLOOKUP 什么时候升级到 XLOOKUP。

为什么 VLOOKUP 这么难记

VLOOKUP 是 Excel 最高频的函数之一，但也是最难记的之一，原因有 3 个：

  1. 4 个参数顺序固定：lookup_value, table_array, col_index_num, range_lookup
  2. col_index_num 要数列：要查的字段在 table_array 的第几列，得人脑数
  3. range_lookup 默认精确模糊：FALSE 是精确匹配，TRUE 是模糊匹配，写反就出错

VLOOKUP 还有 3 个硬伤：

  • 只能从左往右查（查找列必须在结果列左边）
  • 加一列就要重新数 col_index_num
  • 出错信息（#N/A）不告诉你为啥不匹配

Excel 365 推出了 XLOOKUP，4 个硬伤一次性解决。下面的 AI prompt 默认推荐 XLOOKUP，老版本 Excel 才退化到 VLOOKUP。

万能 prompt 模板：用大白话查表

这个 prompt 可以套到任何 AI 工具（Claude / ChatGPT / 豆包 / Kimi / WPS AI）。

📋 Prompt 模板 
你是一位资深 Excel 公式专家，帮我写一个跨表查询公式。
我的需求：
[用大白话描述要查什么，比如「在订单表 A 列填入对应商品的单价」]
数据情况：
源表（要从这里查）：
    

  • sheet 名：[]
    • 

  • 数据范围：[]
    • 

  • 关键列（用来匹配的）：[比如「商品 ID 在 A 列」]
    • 

  • 要返回的列：[比如「单价在 C 列」]
    • 

目标表（要在这里写公式）：
    

  • sheet 名：[]
    • 

  • 我要把公式粘到哪个单元格：[]
    • 

  • 当前行用来匹配的字段：[比如「订单表的 B 列就是商品 ID」]
    • 

Excel 版本：[Excel 365 / Excel 2019 / WPS]
请按以下格式输出：
推荐公式（XLOOKUP 优先）
[直接给出能粘的公式]
备选公式（VLOOKUP 兼容版）
[给一个 VLOOKUP 版本，万一新函数不支持]
公式解释
逐行说明每个参数在做什么。
怎么用
    

  • 粘到哪个单元格
    • 

  • 怎么向下拖
    • 

常见报错处理
    

  • #N/A：[可能原因]
    • 

  • #REF!：[可能原因]
    • 

  • #VALUE!：[可能原因]
    • 

验证样例
给一个 3 行小数据 + 预期结果，让我验证。

把这段粘到 AI 工具，填好方括号，30 秒拿到能用的公式。

5 个真实场景示例

场景 1：基础跨表查询

需求：订单表（OrderSheet）的 B 列是商品 ID，想在 D 列填入商品名（来自商品表 ProductSheet 的 A 列商品 ID + B 列商品名）。

AI 给出：

推荐公式（XLOOKUP）：
=XLOOKUP(B2, ProductSheet!A:A, ProductSheet!B:B, "未找到")

备选公式（VLOOKUP）：
=IFERROR(VLOOKUP(B2, ProductSheet!A:B, 2, FALSE), "未找到")

解释：
- B2：当前订单的商品 ID
- ProductSheet!A:A：在商品表的 A 列查找
- ProductSheet!B:B：找到后返回 B 列的商品名
- "未找到"：如果商品 ID 不存在，显示"未找到"而不是 #N/A

XLOOKUP 比 VLOOKUP 短、清晰、可读，且自带”找不到怎么办”参数。

场景 2：反向查找（VLOOKUP 干不了的事）

需求：商品表的 A 列是商品 ID，B 列是商品名。我已知商品名，想查商品 ID（从右往左查）。

VLOOKUP 直接歇菜——只能从左往右。AI 给出：

推荐公式（XLOOKUP，唯一方案）：
=XLOOKUP("iPhone 15", B:B, A:A, "未找到")

VLOOKUP 干不了，要用 INDEX+MATCH 凑：
=IFERROR(INDEX(A:A, MATCH("iPhone 15", B:B, 0)), "未找到")

XLOOKUP 的优势就出来了——不管查找列在结果列左边还是右边都能用。

场景 3：多条件查询

需求：销售表里要查”2026 年 5 月、销售员张三、商品 iPhone 的订单数量”。

推荐公式（XLOOKUP 数组形式）：
=XLOOKUP(1, (A:A="2026-05")*(B:B="张三")*(C:C="iPhone"), D:D, "无")

或者用 FILTER：
=FILTER(D:D, (A:A="2026-05")*(B:B="张三")*(C:C="iPhone"), "无")

VLOOKUP 干不了（只支持单条件查找），可以用 SUMIFS 替代：
=SUMIFS(D:D, A:A, "2026-05", B:B, "张三", C:C, "iPhone")

如果就是要”加总”，SUMIFS 比 XLOOKUP 更直接。

场景 4：模糊匹配（带通配符）

需求：客户表里查所有以”北京”开头的客户的总销售额。

推荐公式：
=SUMIFS(D:D, A:A, "北京*")

如果要查"找到的第一个北京客户的销售额"：
=XLOOKUP("北京*", A:A, D:D, "无", 2)
（XLOOKUP 第 5 个参数 2 表示通配符匹配）

场景 5：跨多个 sheet 查找

需求：有 3 个区域 sheet（北京 / 上海 / 广州），要在汇总 sheet 里根据”区域 + 商品 ID”查单价。

推荐公式（嵌套 XLOOKUP）：
=XLOOKUP(B2, INDIRECT(A2 & "!A:A"), INDIRECT(A2 & "!C:C"), "未找到")

解释：
- A2 是当前行的"区域"（比如"北京"）
- INDIRECT(A2 & "!A:A") 动态生成「北京!A:A」这样的引用
- B2 是要查的商品 ID
- 结果是该区域 sheet 中对应商品的单价

这种 INDIRECT + XLOOKUP 的组合，对人脑很难想，对 AI 是分分钟。

3 个最常见的报错和救援

#N/A：找不到匹配项

最常见原因：

  1. 空格：源数据 “iPhone15” vs 查找值 ” iPhone15 “（带空格），加 TRIM(B2) 试试
  2. 数据类型：源是文本 “123”，查找值是数字 123（或反之），格式不一致
  3. 拼写：iPhone 15 vs iPhone15，多了或少了空格

救援 prompt：

📋 Prompt 模板 
我上面的 XLOOKUP / VLOOKUP 公式返回 #N/A。
数据情况：
    

  • 查找值：[复制单元格内容]
    • 

  • 源数据第一行匹配值：[复制源 A1 单元格内容]
    • 

请帮我：
    

  1. 诊断可能原因（空格 / 大小写 / 数据类型）
    2. 

  2. 给一个加上 TRIM / CLEAN 等清洗函数的”防错版”公式
    3. 

  3. 给一个能”测试为什么不匹配”的对比公式
    4.

#REF!：列号不存在

VLOOKUP 的 col_index_num 超出了 table_array 的列数。比如 table_array 是 A:C（3 列），你写了 col_index_num = 4。

救援：要么扩大 table_array，要么改用 XLOOKUP（不需要数列）。

#VALUE!：参数类型错

经常是 range_lookup（第 4 个参数）忘了写或者写错。VLOOKUP 强烈建议永远写 FALSE。

更彻底的救援：让 AI 直接换成 XLOOKUP，省掉 range_lookup 这个坑。

手把手 5 步走

第 1 步：搞清两张表的结构

写 prompt 之前，至少要知道：

  • 源表（要从中查）：sheet 名、关键列、要返回的列
  • 目标表（要写公式）：sheet 名、当前行用什么字段去匹配、公式粘到哪里

第 2 步：填好上面的 prompt

填得越具体输出越准。

第 3 步：先在小数据上验证

把公式粘到目标表的 1 行，手动核对结果。对了再扩大到全表。

第 4 步：遇到 #N/A 不要慌

用上面的救援 prompt 让 AI 诊断。99% 的 #N/A 是数据问题不是公式问题。

第 5 步：考虑用辅助列简化

如果一个公式太复杂（多个 XLOOKUP 嵌套），考虑加 1-2 个辅助列拆开来写，可读性 / 维护性都更好。

5 个让 AI 写得更准的技巧

技巧 1：贴一段真实数据

把表的前 3-5 行用 Markdown 表格格式粘到 prompt 里，AI 一眼能看清结构，公式精度暴涨。

技巧 2：明确”表头在哪行”

“数据从第 2 行开始（第 1 行是表头）“这种话写清楚，AI 给出的公式就会从 A2 而不是 A1 开始。

技巧 3：说明 Excel 版本

Excel 2019 不支持 XLOOKUP，要降级到 VLOOKUP/INDEX-MATCH。在 prompt 里加版本能避免拿到不能跑的公式。

技巧 4：要求”如果失败怎么办”

让 AI 在公式里加 IFERROR 兜底，比如返回 “未找到” 而不是 #N/A，可读性强 10 倍。

技巧 5：要求”备选 2 个方案”

让 AI 给 XLOOKUP 版 + VLOOKUP 版，看哪个公司同事更容易看懂就用哪个。

一个常见失败案例 + 怎么救

失败场景：直接发「帮我写 VLOOKUP 公式」就完事。

AI 大概率反问你一堆问题。

救援步骤：

  1. 说清”两张表”：「源表是 Sheet2 商品库，目标表是 Sheet1 订单」
  2. 说清”匹配字段”：「用商品 ID 去匹配，源在 Sheet2 A 列，目标在 Sheet1 B 列」
  3. 说清”返回什么”：「我要返回的是 Sheet2 C 列的单价」

记住：VLOOKUP / XLOOKUP 本质是”两张表 + 一个共同字段 + 要返回的字段”，把这三件事说全就行。

进阶玩法 + 类似场景

进阶玩法 1：升级到 XLOOKUP

如果你的 Excel 是 Excel 365 / Excel 2021 及以上，全部 VLOOKUP 都建议换成 XLOOKUP。让 AI「把这个 VLOOKUP 公式改写成 XLOOKUP」，一键升级。

进阶玩法 2：用辅助列代替复杂公式

复杂的多条件 XLOOKUP 可以拆成 2 步：

  1. 加一个辅助列把多条件拼成一个字符串：=A2 & "|" & B2 & "|" & C2
  2. 用 XLOOKUP 用这个拼接列做匹配

可读性强、维护方便、跑得快。

进阶玩法 3：用 FILTER 代替查找

如果你想拿到”所有符合条件的行”而不只是”第一个匹配项”，用 FILTER 函数：

=FILTER(销售表, (销售表[销售员]="张三")*(销售表[月份]="2026-05"))

直接出一整张筛选后的表，比 XLOOKUP 强。

类似场景

常见问题

Q：VLOOKUP 和 XLOOKUP 我该用哪个？ A：Excel 365 / Excel 2021 都用 XLOOKUP，更简洁、自带兜底、支持反向查找。老版本 Excel（2019 及以下）用 VLOOKUP。WPS 现在也支持 XLOOKUP。

Q：VLOOKUP 怎么”不区分大小写”？ A：VLOOKUP 默认就不区分大小写。如果你要”区分大小写”匹配，要用 INDEX + MATCH + EXACT 组合。让 AI「给一个区分大小写的查找公式」即可。

Q：跨工作簿（不同 Excel 文件）能用 VLOOKUP 吗？ A：能，但两个文件都要打开。公式写法：=VLOOKUP(B2, '[商品库.xlsx]Sheet1'!A:C, 3, FALSE)。关闭源文件后公式会显示 #REF!。建议要么把数据合并到一个文件，要么用 Power Query 建立连接。

Q：VLOOKUP 慢怎么办？ A：用了 FALSE（精确匹配）的 VLOOKUP 在大数据上确实慢。3 个加速方案：1）改用 XLOOKUP，2）改用 INDEX+MATCH，3）把数据排序后用 TRUE（模糊匹配）+ 二分查找。具体让 AI 给方案。

Q：能让 AI 直接生成”完整查询表”吗？ A：能。让 AI「基于商品库表和订单表，生成一个汇总表（每行订单 + 对应商品名 + 单价 + 总价）」，AI 会给出完整公式。具体看 Excel 数据分析教程。