如何用XLOOKUP实现商品精确匹配+日期近似匹配的价格查询？

你需要实现的是：精确匹配商品名称，同时匹配目标日期或取该商品最近的更早日期对应的价格，但原公式返回了错误结果（比如测试中Apple 01/06/2025返回了Banana的价格），核心原因是原公式逻辑错误——它试图在整个数据范围内找同时满足商品和日期完全匹配的项，找不到时会返回任意满足“最接近1”的项（实际是0值对应的行），而非限定在目标商品的范围内找日期。

正确解法 #

方法1：XLOOKUP + FILTER（Excel 365/2021及以上）

利用FILTER先筛选出目标商品的所有日期和价格，再用XLOOKUP在筛选后的日期中做近似匹配：

=XLOOKUP(B1, FILTER(Z:Z, Y:Y=A1), FILTER(X:X, Y:Y=A1),, -1)

• 逻辑拆解：
  1. FILTER(Z:Z, Y:Y=A1)：筛选出所有和A1商品名称匹配的日期
  2. FILTER(X:X, Y:Y=A1)：对应筛选出该商品的所有价格
  3. XLOOKUP的最后一个参数-1：找到精确匹配的日期，若找不到则返回小于目标日期的最大日期对应的价格

方法2：INDEX + XMATCH（兼容更多Excel版本）

用IF限定只在目标商品的日期范围内查找，再通过XMATCH定位对应行：

=INDEX(X:X, XMATCH(B1, IF(Y:Y=A1, Z:Z),, -1))

• 逻辑拆解：
  1. IF(Y:Y=A1, Z:Z)：仅保留目标商品的日期，其他行返回FALSE（会被XMATCH忽略）
  2. XMATCH(B1, ..., -1)：在保留的日期中查找目标日期，找不到则返回最近的更早日期的位置
  3. INDEX根据定位的行号返回对应的价格

原公式错误原因 #

你的原公式=XLOOKUP(1,(A1=Y1:Y10)*(B1=Z1:Z10),X1:X10,0,-1)逻辑不成立：

• 数组(A1=Y1:Y10)*(B1=Z1:Z10)只有两个条件都满足时才会返回1，否则返回0
• 当没有完全匹配的项时，XLOOKUP的-1模式会找小于等于1的最大数值（也就是0），此时会返回任意一个0对应的价格（取决于数据顺序），而非限定在目标商品范围内查找日期

注意事项 #

• 确保日期列（Z列）是真正的日期格式，而非文本格式，否则近似匹配会失效
• 若使用Excel 2019及更早版本，FILTER函数不可用，建议使用方法2

内容的提问来源于stack exchange，提问作者Alain Rivero Duclaud