Excel 怎么用 INDEX 和 MATCH 查找数据
VLOOKUP 只能往右查,往左取就卡住。这篇讲 MATCH 定位行号、INDEX 按位取值怎么配合,区域怎么锁,错误值怎么排查。
表里有一万行订单,想按订单号查出金额,VLOOKUP 却要求查找列必须在最左边。INDEX 配 MATCH 就是绕开这条限制的办法:MATCH 定位在第几行,INDEX 按位置把值取出来。
入口在哪
不用找菜单,直接在单元格里敲公式。想看得清楚点,点编辑栏左边的 fx 按钮,或者走 公式 > 插入函数,分别搜 INDEX 和 MATCH,每个参数都有中文说明。WPS 表格里函数名和参数一致,只是向导措辞略有差别。
两个函数各管一件事。MATCH 在一行或一列里找值,返回它是第几个;INDEX 在区域里按行号列号取值。合起来就是先定位、再取值。
具体步骤
- 先定两块区域。查找区域必须是单独一行或一列,MATCH 不支持二维区域。订单号在 A 列、金额在 C 列,就取 A2:A10000 和 C2:C10000。
- 单独写一个 MATCH 试通:
=MATCH(D2,A2:A10000,0)。第三个参数 0 是精确匹配,必须写,漏掉就走近似匹配。 - 把 MATCH 塞进 INDEX 的行号位置:
=INDEX(C2:C10000,MATCH(D2,A2:A10000,0))。区域只有一列时列号可以不写。 - 横竖都要查就放两个 MATCH:
=INDEX(B2:E10000,MATCH(G2,A2:A10000,0),MATCH(H1,B1:E1,0)),行号找订单号,列号找表头。 - 两个区域改成绝对引用,再往下拖填充,否则下拉时区域会跟着跑偏。
- 查不到会返回错误值,外面套一层 IFERROR,第二个参数填一句提示文字。
几个坑
- 行号是相对区域的偏移量,不是工作表行号。区域从 A2 开始,MATCH 返回 1 指的是 A2。
- 两块区域起点要对齐。一个从第 2 行起、一个从第 1 行起,结果整体错一行,而且不报错。
- 文本和数字看着一样却不相等。导出的单号常带前导空格或是文本格式,用 TRIM 清一下。
- 返回 #N/A 不一定是公式写错,多数是确实没这个值,或者两边格式不一致。
- 漏写第三个参数最隐蔽。这时 MATCH 认为数据已排序,会返回一个接近的值,像查错了行。
怎么验证
写完别只盯着一行,按下面几条过一遍:
- 挑三条已知答案的记录,对着原表核对。
- 拖到区域最底部,成片的错误值通常是区域没锁住。
- 改一下查找单元格,结果应当跟着变。
- 选中公式里的一段按 F9 单独求值,看 MATCH 返回的行号对不对。
常见问题
和 VLOOKUP 差在哪
VLOOKUP 要求查找列在被查区域最左边,只能往右取;INDEX 配 MATCH 不受方向限制,插入删除列之后公式也不容易被改坏,代价是公式更长。
为什么返回 #N/A
先确认查找值确实存在,再比对格式:一边文本一边数字,或者带空格、带看不见的字符,都会判为不等。用等号直接比较两个单元格,返回 FALSE 就是格式不一致。
能不能一次查出整行
可以。把 INDEX 的返回区域写成一整块,列号用 MATCH 定位表头,横向拖填充就能拉出多个字段。注意返回区域锁死,只让表头参数跟着变。
一句话总结:MATCH 找位置、INDEX 按位置取值,第三参数写 0、两块区域起点对齐、区域加绝对引用,三条做到基本不会错。