如何让VLOOKUP调取数据验证列表对应内容并计算折扣价格

前提说明 #

以下为预设的单元格/区域对应关系，你可以根据自己的实际表格调整参数：

• 数据验证（订单号选择）单元格：A1
• 全量订单主数据集范围：$A$2:$Z$1000（首列为订单号，后续列依次存储订单对应属性，列号可根据实际调整）
• 自制月份折扣对照表范围：$L$12:$M$15（首列为月份，第二列为对应折扣率）

方案1：VLOOKUP嵌套实现（兼容所有Excel版本） #

各字段取值公式 #

• 商品描述：=VLOOKUP(A1, $A$2:$Z$1000, 3, FALSE) （3为商品描述在主数据集的列序号，自行替换）
• 供应商：=VLOOKUP(A1, $A$2:$Z$1000, 5, FALSE)（5为供应商在主数据集的列序号，自行替换）
• 发货天数：=VLOOKUP(A1, $A$2:$Z$1000, 10, FALSE)（10为发货天数在主数据集的列序号，自行替换）
• 折扣价：=VLOOKUP(VLOOKUP(A1, $A$2:$Z$1000, 8, FALSE), $L$12:$M$15, 2, TRUE) * VLOOKUP(A1, $A$2:$Z$1000, 7, FALSE)
  • 公式说明：内层第一个VLOOKUP先根据选中的订单号取出对应下单月份，匹配折扣表拿到折扣率，再乘以内层第二个VLOOKUP取出的订单原价，得到最终折扣价。其中8为下单月份在主数据集的列序号，7为原价在主数据集的列序号，自行替换即可。

方案2：XLOOKUP实现（适用Excel 365/2021及以上版本，容错率更高） #

不需要关注主数据集列顺序，只要指定对应列范围即可：

• 基础版折扣价公式：=XLOOKUP(XLOOKUP(A1, 主数据集订单号列, 主数据集月份列), 折扣表月份列, 折扣表折扣列) * XLOOKUP(A1, 主数据集订单号列, 主数据集原价列)
• 优化版（用LET函数减少重复查找，效率更高）：

=LET(
订单原价,XLOOKUP(A1, $A$2:$A$1000, $G$2:$G$1000),
订单月份,XLOOKUP(A1, $A$2:$A$1000, $H$2:$H$1000),
折扣率,XLOOKUP(订单月份, $L$12:$L$15, $M$12:$M$15),
订单原价*折扣率
)

注意事项 #

• 所有固定数据集范围需要加$锁死，避免拖动公式时区域偏移
• 匹配订单号时查找函数最后一个参数需设置为FALSE（精确匹配），避免匹配到错误订单信息
• 折扣表如果使用区间匹配，需确保月份列按升序排列，否则会出现匹配错误

内容的提问来源于stack exchange，提问作者Emily Mitchell