相信经常使用EXCEL的小伙伴们，对VLOOKUP函数并不陌生。这个函数是我们最常用的几个函数之一。相信，小伙伴们，也经常会用到这个函数。这里，咱们玩点其他的。
首先，给大家准备了源数据。如图：
<1> 这里我们通过VLOOKUP函数来实现反向查询。
图1 数据源
如图1，G2 和H2 是我们的需求。要求都使用VLOOKUP函数完成。
H2很好完成，公式H2=VLOOKUP(F2,$B$2:$C$11,2,0).
G2，因为姓名列在源数据中是在员工列的后面，直接使用VLOOKUP函数肯定不行，这里EK给大家把公式直接展示出来以后为小伙伴们分析一下。方便更好地理解。公式 G2 =VLOOKUP(B2,IF({1,0},B2:B11,A2:A11),2,0)
1、 如何理解公式首先，我们来拆解G2的公式，IF({1,0},B2:B11,A2:A11)表示的是2维数组，10行2列。如下图：
图2 数组表现图
2、通过图2，小伙伴们很清楚地理解了。我们只是将B列和A列的顺序通过IF函数来进行调换位置，来达到我们的查询目的。
效果图：
图4 查询效果图
到这里，反向查询已经完成。
<2> 查询一个部门的所有员工
首先，准备了一个数据源：
图5 数据源与需求
这里，给大家准备了2种方法。
第1种方法，需要在A列中增加一列辅助列。
效果图如图所示：
图6 查询部门员工效果图
这里，先分别把公式贴出来，A2=COUNTIF(A2:$A$2,$F$2)，表示，在A2到A2单元格中满足F2的个数，往下填充至A11，这里A11的公式变为，A11=COUNTIF($A$2:A11,$F$2) ， 表示，在A2到A11单元格中满足F2的个数.
H列公式为：H2=IFERROR(VLOOKUP(ROW($A1),$A:$C,COLUMN(B1),0) ,"")
I列公式为：I2=IFERROR(VLOOKUP(ROW($A1),$A:$C,COLUMN(C1),0),"")
公式分析：看到，H2和I2的公式都是差不多的。ROW($A1),返回1，ROW（$A2) 返回2，这里可以使我们的公式更好的重用.由此我们通过VLOOKUP和辅助列，将查找员工号和姓名，转换成查找部门在D2：D当前行号的F2出现的次数ROW。由此达到我们的查找F2部门所有员工的目的。
第2种方法：
如图：
图7 效果图
这里，没有增加辅助列，使用的是数组函数
图8 数组公式
我们可以看到图8 中G2的公式外面有花括号，这里花括号不是手输的。而是在写好的公式上通过三个组合键CTRL SHIFT ENTER一起按得到的。
G2的公式：=IFERROR(VLOOKUP($E$2&ROW(A1),IF({1,0},$C$2:$C$11&COUNTIF(INDIRECT("c2:c"&ROW($2:$11)),$E$2),A$2:A$11),COLUMN($B$1),),"")，在公式写完以后CTRL SHIFT ENTER一起按。
同样，H2的公式：=IFERROR(VLOOKUP($E$2&ROW(B1),IF({1,0},$C$2:$C$11&COUNTIF(INDIRECT("c2:c"&ROW($2:$11)),$E$2),B$2:B$11),COLUMN($B$1),),"")，在公式写完以后CTRL SHIFT ENTER一起按。
然后一起往下填充。效果如图8 所示。
分析公式：
1、H列和G列的公式类似，这里只分析G2，分析之前将G2公式分解。INDIRECT("c2:c"&ROW($2:$11)) ,返回的是单元格引用C2：C2，G3则返回C2：C3。
2、我们将组合函数 $C$2:$C$11&COUNTIF(INDIRECT("c2:c"&ROW($2:$11)),$E$2),B$2:B$11) 是数组函数，我们在I列给大家展示一下。如图所示：
图9 组合函数的数组元素列表
3、IF({1,0},$C$2:$C$11&COUNTIF(INDIRECT("c2:c"&ROW($2:$11)),$E$2),A$2:A$11)，第一个案例里已经说过，我们知道的是返回一个二维数组。如图：
图10 对应的二维数组
4、VLOOKUP($E$2&ROW(B1),IF({1,0},$C$2:$C$11&COUNTIF(INDIRECT("c2:c"&ROW($2:$11)),$E$2),B$2:B$11),COLUMN($B$1),)，G2对应的$E$2&ROW(B1) 值为武技部1，G3对应的$E$2&ROW(B2)值为武技部2，这里相信小伙伴们就能理解了，在图10 的数组中利用VLOOKUP函数查找出对应的工号。
5、最后利用IFFEROR函数对整个函数进行防错，错误值显示“”，对G2和H2中公式往下拉可以得到图8的效果，目的完成。
评论 (0)