Excel多条件查找技巧——vlookup与index+small

1、使用vlookup需要用到辅助列，先在班级左边插入一列，并输入公式：=COUNTIF($B$2:B2,B2)，下拉公式。

2、然后在K2单元格中输入数组公式：=VLOOKUP($J$2&ROW(B1),IF({1,0},$B$2:$B$25&$A$2:$A$25,$C$2:$C$25),2,0)，并按Ctrl+shift+enter，下拉公式便可得到你想要的结果。

3、公式解释1：=COUNTIF($B$2:B2,B2)是返回B2的值在$B$2:B2区域中的个数；

公司解释2：=VLOOKUP($J$2&ROW(B1),IF({1,0},$B$2:$B$25&$A$2:$A$25,$C$2:$C$25),2,0)是利用二班1、二班2、二班3……作为查找值，查找区域为用if函数与数组配合构建的新区域{二班1,A；二班2,B；四班1,C;……}，这样做的目的是使得查找值在查找区域中为唯一值，最后返回查找结果。

1、在J2单元格输入公式：=INDEX(B:B,SMALL(IF($A$2:$A$25=$I$2,ROW($A$2:$A$25),4^8),ROW(A1)))&""，并按Ctrl+Shift+Enter，因为公式中包含数组。

2、选择J2单元格，鼠标放在该单元格右下角，出现“+”时双击下拉公式，结果就出来了。

3、公式解释1：IF($A$2:$A$25=$I$2,ROW($A$2:$A$25),4^8)反映的是如果A2：A25等于I2中的班级，那么，公式返回A2:A25所对应的行数，否则返回4^8，即4的8次方，excel中行数到达4^8就没有行了。整个if函数的结果是一个数组{2；3；65536；65536；5……}。

公式解释2：SMALL(IF($A$2:$A$25=$I$2,ROW($A$2:$A$25),4^8),ROW(A1))反映的是从{2；3；65536；65536……}中取第一个最小数，即为2，单元格下拉后，公式就会依次取第二个最小数3，第三个最小数5……最终得到所有符合I2条件的单元格所在的行数。

公式解释3：INDEX(B:B,SMALL(IF($A$2:$A$25=$I$2,ROW($A$2:$A$25),4^8),ROW(A1)))，反映的是从B列数据中取符合条件的第几行的数据。