INDEX 和 MATCH 多数组应用
假设你有多个表头相同的表格,如下所示,要从这些表格中查找符合给定条件的值可能对你来说是一项艰巨的任务。在本教程中,我们将讨论如何通过 INDEX、MATCH 和 CHOOSE 函数在多个数组、范围或组中查找符合特定条件的值。
如何跨多个数组查找值?
为了了解属于不同部门的不同组的负责人,你可以首先使用 CHOOSE 函数来定位返回负责人姓名的表格。然后,MATCH 函数将找出该负责人在其所属表格中的位置。最后,INDEX 函数将根据位置信息以及列出负责人姓名的具体列检索出该负责人。
通用语法
=INDEX(CHOOSE(array_num,array1,array2,…),MATCH(lookup_value,lookup_array,0),column_num)
- array_num: CHOOSE 用来指示列表 array1,array2,… 中返回结果的数组的编号。
- array1,array2,…: 返回结果的数组。这里指三个表格。
- lookup_value: 组合公式用来查找其对应负责人的位置的值。这里指的是给定的组。
- lookup_array: 列出 lookup_value 的单元格范围。这里指的是组范围。 注意:由于所有部门的组范围都相同,我们只需要获取位置编号,因此可以使用任何部门的组范围。
- column_num: 指定从中检索数据的列。
要了解属于 A 部门的 D 组的负责人,请在 G5 单元格中复制或输入以下公式,并按 Enter 键以获取结果:
=INDEX(CHOOSE(1,$B$5:$C$8,,$B$11:$C$14,,$B$17:$C$20),MATCH(F5,$B$5:$B$8,0),2)
√ 注意:上面的美元符号 ($) 表示绝对引用,这意味着当你将公式移动或复制到其他单元格时,公式中的名称和类别范围不会改变。输入公式后,向下拖动填充柄以将公式应用于下方的单元格,然后相应地更改 array_num。
公式解释
=INDEX(CHOOSE(1,$B$5:$C$8,$B$11:$C$14,$B$17:$C$20),MATCH(F5,$B$5:$B$8,0),2)
- CHOOSE(1,$B$5:$C$8,$B$11:$C$14,$B$17:$C$20): CHOOSE 函数从公式中列出的三个数组中返回第 1 个数组。因此它将返回 $B$5:$C$8,即 A 部门的数据范围。
- MATCH(F5,$B$5:$B$8,0): 匹配类型 0 强制 MATCH 函数返回 F5 单元格中 D 组 的第一个匹配项在数组 $B$5:$B$8 中的位置,即 4。
- INDEX(CHOOSE(1,$B$5:$C$8,$B$11:$C$14,$B$17:$C$20),MATCH(F5,$B$5:$B$8,0),22) = INDEX($B$5:$C$8,4,22): INDEX 函数检索范围 $B$5:$C$8 中第 4 行与第 2 列交叉处的值,即 Emily。
为了避免每次复制公式时都要更改公式中的 array_num,你可以使用辅助列,即 D 列。公式将如下所示:
=INDEX(CHOOSE(D5,$B$5:$C$8,,$B$11:$C$14,,$B$17:$C$20),MATCH(F5,$B$5:$B$8,0),2)
√ 注意:辅助列中的数字 1、2、3 表示 CHOOSE 函数内的 array1、array2、array3。
相关函数
Excel INDEX 函数基于给定的范围或数组中的位置返回显示的值。
Excel MATCH 函数在一个单元格范围内搜索特定值,并返回该值的相对位置。
CHOOSE 函数根据给定的索引号从值参数列表中返回一个值。例如,CHOOSE(3,”Apple”,”Peach”,”Orange”) 返回 Orange,索引号为 3,而 Orange 是函数中索引号后的第三个值。
相关公式
如果你知道如何使用 VLOOKUP 函数在工作表中搜索值,那么从另一个工作表或工作簿中 vlookup 值对你来说就不是问题了。
在许多情况下,你可能需要跨多个工作表收集数据进行汇总。通过结合 VLOOKUP 函数和 INDIRECT 函数,你可以创建一个公式,利用动态工作表名称跨工作表查找特定值。
当处理具有多列和行标题的大型数据库时,在 Excel 表格中查找符合多个条件的内容总是很棘手。在这种情况下,你可以使用带有 INDEX 和 MATCH 函数的数组公式。
最佳的办公生产力工具
Kutools for Excel - 助您脱颖而出
🤖 | Kutools AI 助手:基于以下功能彻底改变数据分析方式:智能执行 | 生成代码 | 创建自定义公式 | 分析数据并生成图表 | 调用Kutools函数… |
热门功能: 查找、高亮或标记重复项 | 删除空行 | 合并列或单元格而不丢失数据 | 四舍五入无需公式 ... | |
超级VLookup: 多条件 | 多值 | 跨多个工作表 | 模糊查找... | |
高级下拉列表: 简易下拉列表 | 级联下拉列表 | 多选下拉列表... | |
列管理器: 添加指定数量的列 | 移动列 | 切换隐藏列的可见状态 | 比较列以 选择相同和不同的单元格 ... | |
精选功能:网格聚焦 | 设计视图 | 增强编辑栏 | 工作簿与工作表管理器 | 资源库(自动文本) | 日期提取 | 合并数据 | 加密/解密单元格 | 按列表发送电子邮件 | 超级筛选 | 特殊筛选(筛选粗体/斜体/删除线...) ... | |
排名前15的工具集: 12个 文本 工具(添加文本,删除特定字符 ...) | 50多种 图表 类型(甘特图 ...) | 40多种实用 公式(基于生日计算年龄 ...) | 19个 插入 工具(插入二维码,从路径插入图片 ...) | 12个 转换 工具(小写金额转大写,汇率转换 ...) | 7个 合并与拆分 工具(高级合并行,分割Excel单元格 ...) | ... 还有更多 |
Kutools for Excel拥有超过300种功能,确保您需要的功能只需一键即可实现...
Office Tab - 在Microsoft Office(包括Excel)中启用标签式阅读和编辑
- 一秒内切换数十个打开的文档!
- 每天为您减少数百次鼠标点击,告别鼠标手。
- 在查看和编辑多个文档时,您的工作效率将提高50%。
- 为Office(包括Excel)带来高效的标签页,就像Chrome、Edge和Firefox一样。