Excel如何更简洁使用IFS函数 查找B列值为1时返回对应A列内容

需求背景 #

• 操作对象：名为8&Under的工作表
• 实现逻辑：在工作表B列定位值为1的单元格，返回该单元格所在行A列的对应值
• 原有方案问题：逐行写IFS判断的公式冗余度高，不利于后续同类逻辑复用

原有可正常运行但篇幅过长的公式：

=IFS('8&Under'!B3=1,'8&Under'!A3,'8&Under'!B4=1,'8&Under'!A4,'8&Under'!B5=1,'8&Under'!A5,'8&Under'!B6=1,'8&Under'!A6,'8&Under'!B7=1,'8&Under'!A7,'8&Under'!B8=1,'8&Under'!A8,'8&Under'!B9=1,'8&Under'!A9,'8&Under'!B10=1,'8&Under'!A10,'8&Under'!B11=1,'8&Under'!A11)

简洁实现方案 #

根据你使用的Excel版本选择对应公式即可，无需逐行编写判断条件。如果仅需要在原公式覆盖的B3:B11区间查找，把公式里的整列引用B:B/A:A替换为B3:B11/A3:A11即可，计算效率更高。

适用于Excel 365/2021及以上支持动态数组的版本 #

• 仅返回第一个匹配到1的对应A列值，用XLOOKUP，逻辑直观易修改：

=XLOOKUP(1,'8&Under'!B:B,'8&Under'!A:A,"无匹配结果")

公式最后一个参数为无匹配值时的返回内容，可根据实际需求自定义修改

• 需要返回所有B列值为1对应的A列结果（B列存在多个1时会自动溢出展示全部结果），用FILTER：

=FILTER('8&Under'!A:A,'8&Under'!B:B=1,"无匹配结果")

适用于Excel 2019及更早的旧版本（无动态数组功能） #

用兼容性最好的INDEX+MATCH组合，不受列位置限制，默认返回第一个匹配值的对应结果：

=IFERROR(INDEX('8&Under'!A:A,MATCH(1,'8&Under'!B:B,0)),"无匹配结果")

外层嵌套IFERROR是为了在无匹配结果时返回自定义提示，避免显示#N/A错误值

复用方法 #

后续同类场景只需要替换公式里的3个核心参数即可快速套用：

• 查找目标值：比如要查找数值2，就把公式里的1替换为2
• 查找列范围：替换为目标值所在的列/单元格区间
• 返回列范围：替换为需要提取内容的列/单元格区间

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