Excel 面试 05 查找函数组合 INDEX-MATCH
Excel 的 INDEX-MATCH 是一种强大的函数组合，用于查找和返回表格中的值。相比于传统的 VLOOKUP 或 HLOOKUP，它更灵活且高效，尤其在需要双向查找或处理动态列时表现出色。
INDEX-MATCH 基本原理
-
INDEX 函数：返回数组中指定位置的值。
语法：
INDEX(array, row_num, [column_num])- array：要从中取值的范围或数组。
- row_num：指定返回值的行号。
- column_num（可选）：指定返回值的列号。
示例：
=INDEX(A1:C3, 2, 3)- 查找
A1:C3范围的第 2 行第 3 列的值。
-
MATCH 函数：返回查找值在数组中的位置。
语法：
MATCH(lookup_value, lookup_array, [match_type])- lookup_value：需要查找的值。
- lookup_array：要搜索的范围。
- match_type（可选）：
1：小于等于查找值的最大值（默认，需按升序排序）。0：精确匹配。-1：大于等于查找值的最小值（需按降序排序）。
示例：
=MATCH(90, B2:B5, 0)- 返回
B2:B5范围中90的位置（精确匹配）。
-
INDEX-MATCH 组合：使用 MATCH 确定位置，再用 INDEX 返回对应值。
INDEX-MATCH 语法
=INDEX(return_array, MATCH(lookup_value, lookup_array, match_type))
- return_array：要返回值的范围。
- lookup_value：要查找的值。
- lookup_array：包含查找值的范围。
- match_type：指定匹配方式（通常为
0，精确匹配）。
INDEX-MATCH 的优点
-
支持左侧查找：
- VLOOKUP 只能从左到右查找，INDEX-MATCH 没有这个限制。
-
动态列查找：
- VLOOKUP 依赖列索引号，数据表发生变化时容易出错；而 INDEX-MATCH 不受列位置影响。
-
更高效：
- 当数据范围很大时，INDEX-MATCH 运行速度比 VLOOKUP 快。
-
支持水平查找：
- 配合
MATCH的列号参数，INDEX-MATCH 可以在二维表中查找。
- 配合
用法示例
示例 1：简单查找
| 产品 | 价格 |
|---|---|
| 苹果 | 5 |
| 香蕉 | 3 |
| 葡萄 | 8 |
公式：
=INDEX(B2:B4, MATCH("香蕉", A2:A4, 0))
MATCH("香蕉", A2:A4, 0)返回2，即 “香蕉” 在第 2 行。INDEX(B2:B4, 2)返回3，即对应的价格。- 结果：
3。
示例 2：双向查找
| 产品 | 价格 | 库存 |
|---|---|---|
| 苹果 | 5 | 50 |
| 香蕉 | 3 | 30 |
| 葡萄 | 8 | 80 |
目标：查找 “葡萄” 的库存。
公式：
=INDEX(C2:C4, MATCH("葡萄", A2:A4, 0))
MATCH("葡萄", A2:A4, 0)找到 “葡萄” 的行号。INDEX(C2:C4, 3)返回库存80。- 结果：
80。
示例 3：多条件查找
| 姓名 | 科目 | 分数 |
|---|---|---|
| 张三 | 数学 | 90 |
| 李四 | 英语 | 85 |
| 张三 | 英语 | 88 |
目标：查找 “张三” 在 “英语” 的分数。
公式：
=INDEX(C2:C4, MATCH(1, (A2:A4="张三")*(B2:B4="英语"), 0))
(A2:A4="张三")和(B2:B4="英语")创建两个条件。MATCH(1, ..., 0)确定满足条件的行号。INDEX(C2:C4, ...)返回对应的分数。- 结果：
88。
注意：按
Ctrl + Shift + Enter确认数组公式（适用于旧版 Excel）。
示例 4：从右到左查找
| 价格 | 产品 |
|---|---|
| 5 | 苹果 |
| 3 | 香蕉 |
| 8 | 葡萄 |
目标：查找价格为 3 的产品。
公式：
=INDEX(B2:B4, MATCH(3, A2:A4, 0))
MATCH(3, A2:A4, 0)返回2，即价格3的行号。INDEX(B2:B4, 2)返回 “香蕉”。- 结果：
香蕉。
INDEX-MATCH 和 VLOOKUP 比较
| 功能 | INDEX-MATCH | VLOOKUP |
|---|---|---|
| 查找方向 | 左、右均支持 | 只能从左到右 |
| 灵活性 | 高，支持动态列 | 低，列索引容易出错 |
| 性能 | 更快（尤其大范围时） | 较慢 |
| 复杂性 | 略复杂，需要组合公式 | 简单 |
| 错误处理 | 可自定义错误处理 | 需结合 IFERROR |
总结
INDEX-MATCH 是一个高效、灵活的查找组合，适用于需要精确查找、左侧查找、多条件匹配或更高性能的场景。尽管设置公式稍微复杂，但其强大的功能使其成为 Excel 数据处理的最佳工具之一。