Excel 怎么用 XLOOKUP 按商品编号查单价?

在 Cue Office 打开 Excel 采购单(.xlsx),用 XLOOKUP 到价目表精确匹配商品编号,再把同一行的单价填回采购单。

按商品编号回填单价

在 Purchase Order 工作表中选中 C2。这一行要查的编号在 A2。像 0042 这种开头带零的编号,两张表里都要存成文本。

先试一条时,只粘贴下方第一行公式。想一次填好 C2:C6,就复制五行公式,选中网格里的 C2 后粘贴。

=XLOOKUP(A2,'Price List'!$A$2:$A$5,'Price List'!$C$2:$C$5,"Not found",0)
=XLOOKUP(A3,'Price List'!$A$2:$A$5,'Price List'!$C$2:$C$5,"Not found",0)
=XLOOKUP(A4,'Price List'!$A$2:$A$5,'Price List'!$C$2:$C$5,"Not found",0)
=XLOOKUP(A5,'Price List'!$A$2:$A$5,'Price List'!$C$2:$C$5,"Not found",0)
=XLOOKUP(A6,'Price List'!$A$2:$A$5,'Price List'!$C$2:$C$5,"Not found",0)
名称框显示 C2,公式栏可见 XLOOKUP 开头,下方显示 Unit price 列
名称框显示 C2,右侧公式栏显示 XLOOKUP。点图可看完整公式。

公式先到 Price List!A2:A5 查编号,再从 Price List!C2:C5 取同一行的单价。两个范围覆盖价目表的相同行,所以找到第几条编号,就取第几条价格。每行只换采购单的编号引用(A2、A3……),价目表的两个范围用 $ 固定。

公式最后的 0 只接受精确匹配,找不到时显示 Not found。看到这条提示,先回价目表找这个编号。如果一边是文本 0042、另一边是数字 42,先把两边的编号统一为保留前导零的文本。若编号刚加到第 6 行,还要把查找范围和单价范围一起扩到第 6 行。

核对缺失编号与改价结果

粘贴公式后,保存工作簿并重新打开,检查 Purchase Order 工作表 C2:C6 的回填数值:编号 0042 对应的 C2 与 C5 均显示为 4.25,0047 显示为 8.50,0060 为 1.80;而 0099 由于价目表中未收录,如期显示为 Not found。

重开采购单后,两个 0042 的单价是 4.25,0099 显示 Not found
采购单重开后,C2:C6 的五条结果仍在。

想知道改价后采购单会不会跟着变,可以另存一份副本,把 Price List!C2 从 4.25 改成 5.75。切回采购单,检查两笔 0042 是否都更新,0099 是否仍显示 Not found。

改价后采购单里两条 0042 的单价均变为 5.75
调价副本里,C2 和 C5 都从 4.25 更新为 5.75。

让 Cue AI 按编号补单价

收到新采购单时,可以把查价这一步交给 Cue AI。在原始工作簿的副本里,从 Home 打开 Cue AI,告诉它去哪张表找价格、把结果填到哪里。

Home 标签下的 Cue AI 按钮与已打开的 Cue AI 面板
在 Home 工具栏点 Cue AI,打开左侧面板发送请求。
能帮我按价目表把这张采购单的单价补上吗?如果价目表改价,采购单里也要跟着更新。请按商品编号精确匹配,保留编号开头的零。把结果填到 Purchase Order 的 C2:C6,价目表里没有的编号写成“Not found”,工作簿的其他地方保持原样。

Cue AI 写入后,保存并重开副本。点 C2,在公式栏检查它写入的公式。本例用 VLOOKUP 做精确匹配,并用 IFERROR 显示缺价提示。再确认 C4 显示 Not found,编号前导零和采购数量仍在。

重开由 Cue AI 填写的采购单,C2:C6 显示单价或 Not found
重开 AI 副本后,核对 Unit price 列的五条结果。

再另存一份副本测试调价。把 Price List!C2 从 4.25 改成 5.75 后切回采购单。本例两笔 0042 都更新了,0099 仍显示 Not found。

AI 调价副本里两笔 0042 同时变为 5.75,0099 仍为 Not found
调价副本的 C2 和 C5 都显示 5.75。

用社区修缮采购单练习

展开完整案例

社区修缮小组用这两张表记录采购数量和单价。在同一工作簿建立 Purchase Order 和 Price List 两张表。粘贴前,分别点两张表的 A 列标,在 Home 的数字格式下拉菜单里选 Text,保留编号开头的零。再从两张表的 A1 分别粘贴对应的表头和数据。表名只用来区分两组数据。粘贴后确认编号仍显示为 0042、0047 等四位字符。

Purchase Order
Item ID	Quantity	Unit price
0042	3
0047	2
0099	1
0042	4
0060	2

Price List
Item ID	Item	Unit price
0042	Door hinge	4.25
0047	Sealant	8.50
0060	Wall anchor	1.80
0061	Brush	3.15
采购单原始五条记录,Unit price 列待填写
Purchase Order 记录要采购的编号和数量。
价目表中的四个商品、商品编号与单价
Price List 按 Item ID 维护商品单价。

把查到的单价算成采购金额

可以请 Cue AI 给采购单加一列金额,把数量乘以单价,并列出还需向供应商询价的编号。先抽查第一笔 0042,3 件乘以 4.25 应是 12.75。像 0099 这样仍然缺价的行,等拿到报价再计入预算。

收到供应商的新报价后,在 Cue Office 免费打开 PDF(.pdf)核对商品编号和价格,再更新 Excel(.xlsx)价目表。要把预算交给审批人,可以用免费的编辑工具补齐 Word(.docx)申请或 PowerPoint(.pptx)汇报。 免费下载 Cue Office