Excel用INDEX+MATCH匹配最低成本/交期对应供应商报错求助

问题背景 #

我需要制作两个表格，分别按最低成本、最短交期展示零件报价，需跨所有供应商专属工作表查找对应值。已通过=SMALL('Summary:Vendor Template'!I24,1)获取到最低交期和成本，但使用INDEX+MATCH返回对应供应商名称时出现#VALUE!错误。当前公式为：

=INDEX('Summary:Vendor Template'!C25,MATCH(I25,'Summary:Vendor Template'!I25,0))

其中I25为跨工作表成本单元格，C25为需返回供应商名称的单元格。

补充示例：现有4个工作表：Summary、Test1、Test2、Vendor Template。Test1中零件223供应商为test1，总成本150；Test2中零件223供应商为test2，总成本110。Summary已通过SMALL函数得到最低总成本110，需返回对应供应商名称test2。

错误原因 #

公式引用的是单个单元格（C25、I25），而非包含所有数据的连续区域。MATCH需要在完整的数值区域中查找目标值，INDEX也需要对应到完整的供应商名称区域，才能匹配到正确结果。

解决方案 #

1. 基于汇总表的常规INDEX+MATCH公式 #

假设Vendor Template工作表中，供应商名称存于C列（如C2:C100），总成本存于I列（如I2:I100），在Summary工作表的目标单元格输入：

=INDEX('Vendor Template'!C:C,MATCH(I25,'Vendor Template'!I:I,0))

确保Vendor Template中的C列和I列是所有供应商数据的完整汇总。

2. 直接跨多工作表查找（无需汇总表） #

如果不想通过Vendor Template中转，可使用数组公式（旧版Excel需按Ctrl+Shift+Enter确认）：

=INDEX(CHOOSE({1,2},Test1!C:C,Test2!C:C),MATCH(I25,CHOOSE({1,2},Test1!I:I,Test2!I:I),0))

3. 简化方案（Excel 365/2021动态数组） #

用XLOOKUP函数更高效，直接跨表合并区域查找：

=XLOOKUP(I25,VSTACK(Test1!I:I,Test2!I:I),VSTACK(Test1!C:C,Test2!C:C))

若数据已汇总到Vendor Template，可简化为：

=XLOOKUP(I25,'Vendor Template'!I:I,'Vendor Template'!C:C)

额外说明 #

• 若存在多个相同的最低成本，MATCH会返回第一个匹配的供应商；需返回所有匹配项时，用FILTER函数（仅Excel 365支持）：

  =FILTER('Vendor Template'!C:C,'Vendor Template'!I:I=I25)
  

• 确保查找区域与返回区域的行数完全对应，避免引用不连续或长度不一致的区域。

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