最新

Excel 怎么用 INDEX 和 MATCH 做双向查找

表格横竖都是矩阵,VLOOKUP 只能往右找。这篇讲 INDEX 和 MATCH 怎么配合、参数怎么填、几处容易错位,以及怎么核对结果。

表格行列都排成矩阵,要取某个型号某个季度的数值,用 VLOOKUP 只能往右找,换个方向就得把整张表重排一遍。INDEX 加 MATCH 的组合能按行号和列号各找一次,横竖都能查。这篇讲两个函数各自管什么、怎么套在一起、几处最容易错位的地方,以及怎么确认取到的是那一格。

入口在哪

两个都是公式函数,不在菜单里。点「公式」选项卡里的插入函数,分类选「查找与引用」就能看到 INDEX 和 MATCH。更快的做法是直接在单元格里输入等号再敲函数名,按 Tab 补全,函数提示框会逐个标出参数。

不同版本名字一致,提示框的措辞和参数分组略有差别,不影响填法。区域引用可以直接用鼠标框选,框选后记得按一下功能键把引用锁住,不然后面复制公式会跑偏。

具体步骤

  1. 先分清楚三块区域:结果区是要取的数值所在的矩形,行表头是纵向的关键字,列表头是横向的关键字。三块各自框选,别混在一起。
  2. 用 MATCH 找行号:第一个参数写要查的行关键字,第二个参数写行表头区域,第三个参数写 0 表示精确匹配。
  3. 用 MATCH 找列号:同样三个参数,第二个换成列表头区域。
  4. 把两个结果塞进 INDEX:第一个参数是结果区,第二个是行号,第三个是列号。
  5. 行表头和列表头区域都加绝对引用符号,公式往下拖的时候这两块不能跟着动。
  6. 先只做一个组合试算,手算对得上再往下拖。一次拖满屏再找错,很难定位。

几个坑

  • MATCH 第三参数没写 0。不写就走近似匹配,查找区域没排序时返回的位置看着像随机数。
  • 行表头区域忘了锁。往下拖一格,引用范围整体位移一格,越拖越错,最后整列都是错的。
  • 结果区和表头没对齐。结果区第一行必须对应行表头第一个值,错开一行就整体取到相邻的那一格。
  • 关键字里有空格或全角字符。看着一样其实不相等,精确匹配也会返回错误值。
  • 关键字重复。MATCH 只返回第一次出现的位置,重复项后面的永远取不到。
  • 表头是合并单元格。合并区域的取值位置固定在左上角,MATCH 算出来的坐标会偏。

怎么验证

挑三组已知答案的组合手算对照,三组都对再批量拖。把行关键字和列关键字互换再查一次,结果应该落在对称的位置上,能顺带验证行列没接反。最后故意写一个不存在的关键字,公式应该返回错误值而不是随便给个数。

常见问题

两个 MATCH 的顺序能反吗

谁先谁后不影响结果,但要对上 INDEX 的第二第三参数:第二个是行号,第三个是列号。位置放反了会取到转置的那一格,看着有数其实是错的。

能不能一次查多行多列

可以。把行关键字和列关键字都写成一片区域,公式会返回同样大小的矩阵。较新的版本会自动溢出到相邻单元格,老版本可能需要先选好区域再确认。

查不到为什么不是空白

找不到时 MATCH 返回错误值,整个公式就跟着报错。外面套一层判断函数,把错误值换成「未找到」或空字符串,表格看起来会干净很多。

一句话总结:INDEX 负责指哪打哪,MATCH 负责算出行列坐标,两件事分开做,横竖都能查;真正要防的只有区域没锁和表头没对齐这两处。