Excel函数|告别VLOOKUP限制!ROW + INDEX/MATCH组合,Excel查找天花板
上一期我们聊了ROW函数，这期来讲一下它的进阶组合用法。很多同学学Excel查找函数，第一个接触的就是VLOOKUP。它确实好用，但局限也很明显：只能从左往右查、查找值必须在区域第一列、插入列后公式容易报错……而INDEX + MATCH + ROW这套组合，能完美避开VLOOKUP的所有痛点，实现任意方向的查找、动态数据提取、自动扩展查找范围。在讲组合之前，先快速认识INDEX和MATCH这两个函数。INDEX的作用是：在一个区域里，指定第几行、第几列，把那个位置的值取出来。比如 =INDEX(A1:C10, 3, 2)，就是在A1到C10这个区域里，取第3行第2列交叉点的值。MATCH的作用正好反过来：在一个区域里，找某个值排在第几位。比如 =MATCH("张三", A1:A10, 0)，就是在A1到A10里找“张三”排在第几行。它俩的关系就是：MATCH找到位置，INDEX根据位置取值，天生一对。二、INDEX + MATCH 组合：比VLOOKUP强在哪场景：根据员工姓名查编号，但姓名在B列，工号在A列（从右往左查）。VLOOKUP默认只能从左往右查，遇到这种情况就傻眼了。但INDEX加MATCH轻松搞定：=INDEX(A:A, MATCH("张三", B:B, 0))意思：在B列找到“张三”在第几行，然后去A列把同行的工号取出来。VLOOKUP做不到的，它能做到。VLOOKUP能做到的，它也能做到，而且更稳。INDEX + MATCH已经很强了，加上ROW会发生什么？场景：根据“销售部”这个条件，把部门里所有员工的名字都列出来。现在你想输入“销售部”，自动列出所有销售部员工的名字。在空单元格输入以下公式，输入完成后必须按Ctrl+Shift+Enter结束（Excel会自动在公式外面加一层大括号{}）：=IFERROR(INDEX(B:B, SMALL(IF(C:C="销售部", ROW(C:C)), ROW(1:1))), "")⚠️ 特别注意：Ctrl+Shift+Enter：这个公式里有IF函数对整列做条件判断，返回的是一个数组（多个结果），不是单个值。普通公式只处理一个值，数组公式能同时处理一组值。按Ctrl+Shift+Enter就是告诉Excel：“我要处理一组数据，你给我挨个算一遍。”如果只按Enter，公式不会报错，但结果永远是第一个符合条件的值，下拉也不变，完全失效。IF(C:C="销售部", ROW(C:C))：判断C列每一行是不是“销售部”，是的话就返回该行的行号，不是就返回FALSE。这一步会生成一个行号列表，比如{2, FALSE, 4, 5, FALSE……}SMALL(..., ROW(1:1))：从上面那个行号列表里，取第1小的值，也就是第一个符合条件的行号。下拉到第二行时，ROW(1:1)变成ROW(2:2)，取第2小的值，即第二个符合条件的行号。以此类推，逐个取出所有符合条件的行号。INDEX(B:B, ...)：根据取到的行号，去B列把对应的姓名取出来。IFERROR(..., "")：如果所有符合条件的行号都取完了，SMALL找不到更多值会报错，IFERROR把错误变成空白，避免表格里满屏的#NUM!。效果：输入“销售部”，张三、王五、赵六自动列出来。换“财务部”，自动换一批。全程不用手动筛选、不用复制粘贴。如果你是Excel 365或Office 2021用户，恭喜你，再也不用记Ctrl+Shift+Enter了。=FILTER(B:B, C:C="销售部", "")意思：筛选B列，条件是C列等于“销售部”，没有符合条件的就显示空白。直接按Enter即可，不需要三键结束，结果自动溢出到下方单元格。简单记：VLOOKUP适合“查一个点”，INDEX+MATCH+ROW适合“查一片面”。今天的小作业：找一张带部门或分类的数据表，试着用INDEX+SMALL+IF+ROW这个组合，按条件把符合要求的数据全部提取出来。记住按Ctrl+Shift+Enter，第一次写可能会报错，多调试几次，成功后你会有种“打通任督二脉”的感觉。
