
订单表里只有 SKU,商品名称和价格放在另一张表时,可以用 XLOOKUP 自动带回对应字段。本文使用虚构的商品和订单演示,先从精确查找开始,再处理重复记录和多条件。
先检查 Excel 版本
Microsoft 365、Excel 2021 和 Excel 2024 等版本提供 XLOOKUP。Excel 2016 和 Excel 2019 不支持该函数,收到工作簿时需要确认接收方版本。出现函数名称无法识别时,先查版本与函数拼写。
六个参数各负责什么
基本结构如下。可选参数放在后面,省略时采用默认行为。
=XLOOKUP(查找值,查找区域,返回区域,[找不到时返回],[匹配模式],[搜索模式])
查找区域与返回区域的对应维度要一致。默认执行精确匹配,从头向后找,重复值通常返回第一条匹配。没有提供“找不到时返回”且无匹配时,会返回 #N/A。
用 SKU 跨表查价格
在“商品表”的 A2:C4 输入下表,A 列为 SKU、B 列为名称、C 列为单价。SKU 全部按文本保存。
| SKU | 名称 | 单价 |
|---|---|---|
| 001 | 便签套装 | 15 |
| 002 | 文件夹 | 28 |
| 003 | 台历 | 45 |
在“订单表”B2 输入 SKU 002,C2 查名称,D2 查单价。以下公式使用逗号作为参数分隔符,部分地区设置需要分号。
=XLOOKUP(B2,商品表!$A$2:$A$4,商品表!$B$2:$B$4,"未找到")
=XLOOKUP(B2,商品表!$A$2:$A$4,商品表!$C$2:$C$4,"未找到")
按示例数据应分别得到“文件夹”和 28。查不存在的 SKU 999,应显示“未找到”。这是示例预期结果,实际使用时核对本机计算结果和数据范围。
多条件匹配与重复订单
例如“明细表”A 列是订单号,B 列是 SKU,C 列是实付金额,需要同时匹配两个条件。每个布尔比较得到真或假,相乘后仅全部满足的行得到 1。
=XLOOKUP(1,(明细表!$A$2:$A$101=A2)*(明细表!$B$2:$B$101=B2),明细表!$C$2:$C$101,"未找到")
如果同一订单、同一 SKU 有多笔事件,这个公式仍只返回一条。需要求合计时按业务口径使用 SUMIFS,需要全部匹配行时考虑 FILTER。先检查主键是否唯一,再选函数。
从最后一条记录向前找
在按业务时间排好序的数据中,搜索模式 -1 可以从末尾开始查找。
=XLOOKUP(B2,商品表!$A$2:$A$101,商品表!$C$2:$C$101,"未找到",0,-1)
倒序找到的是表格中最后一个匹配项。若数据未按时间排序,它未必代表最新价格。近似匹配和二分搜索各有额外条件,普通订单对账先使用精确匹配。
错误排查
| 表现 | 检查方法 |
|---|---|
| SKU 看着相同但匹配不到 | 核对文本与数字、前后空格、不可见字符 |
| 001 变成 1 | 从原始文件按文本重新导入 |
| 返回错行 | 检查重复主键和两个区域是否错位 |
| 查到空白返回 0 | 确认源数据确实为空,按业务需要显式处理 |
| #VALUE! | 检查查找与返回区域尺寸 |
| 公式无法使用 | 检查 Excel 版本与地区分隔符 |
不要用总括的 IFERROR 隐藏所有错误后直接交付,应保留未匹配清单,逐项对照源数据。CSV 编码与编号问题见CSV 中文乱码修复,多文件汇总见Power Query 合并教程。
交付前复核
使用不存在的编号、重复编号、空单价、带前导零的 SKU 和文本数字各验证一次。保存公式、输入和预期结果,向接收方确认 Excel 版本。旧版 Excel 可用 INDEX 与 MATCH 组合,根据实际布局调整区域。
评论