excel多条件筛选(excel多条件筛选函数)

Excel中的多条件查找，其实不是很难，很多小伙伴都知道查找用lookup、vlookup函数，但具体怎么使用却不知所措。

今天跟大家分享多条件查找最常用的8个方法，如果你以前不懂，现在看看这篇文章，绝对可以给你带来收获~

下图是一个学科成绩表，我们需要通过左表的姓名和学号两个条件在右表中查找对应的成绩并返回到左表的E列中。

方法一：使用LOOKUP函数。

具体操作步骤如下：

2、动图演示如下。

3、公式解析。

（1）(A2=$G$2:$G$11)(B2=$H$2:$H$11)：

首先，将A2单元格的内容与G2:G11单元格区域的内容作对比，将B2单元格的内容与H2:H11单元格区域的内容作对比。
如果A2单元格的内容与G2:G11单元格区域的内容相等，B2单元格的内容与H2:H11单元格区域的内容相等，则返回TRUE，不相等时，返回FALSE。
根据逻辑值TRUE=1，FALSE=0，所以这部分公式得到的结果可能有3种情况：01；11；10。
公式A2=$G$2:$G$11返回的结果为{0;0;0;0;0;1;0;0;0;0}，因为只有G6单元格的值与A2相等。
公式B2=$H$2:$H$11返回的结果为{0;0;0;0;0;1;0;0;0;0}，因为只有H6单元格的值与B2相等。
所以公式(A2=$G$2:$G$11)(B2=$H$2:$H$11)返回的结果为{0;0;0;0;0;1;0;0;0;0}。

（2）0/(A2=$G$2:$G$11)(B2=$H$2:$H$11)：

LOOKUP函数，如果要精确查找，第2个参数查找区域必须升序排序，得到的结果才是正确的。
但我们这里没有升序排序，用到的是LOOKUP函数的二分法原理，用0来除以(A2=$G$2:$G$11)(B2=$H$2:$H$11)这个公式的结果值，这里只会产生两种情况：0/0或0/1。
而在除法运算中，被除数不能为0，也就是分母不能为0，所以在Excel中，0/0会得到错误值DIV/0!，而0/1的结果为0。
所以该公式返回的结果为{DIV/0!;DIV/0!;DIV/0!;DIV/0!;DIV/0!;0;DIV/0!;DIV/0!;DIV/0!;DIV/0!}。

（3）=LOOKUP(1,0/(A2=$G$2:$G$11)(B2=$H$2:$H$11),$I$2:$I$11)：

根据第（2）步公式返回的结果{DIV/0!;DIV/0!;DIV/0!;DIV/0!;DIV/0!;0;DIV/0!;DIV/0!;DIV/0!;DIV/0!}，再根据LOOKUP函数的查找原理，忽略错误值查找，所以该公式的意思是，找到与1最接近的值，在第（2）步返回的结果数组中，错误值被忽略，只有一个0，0<1，因此返回I2:I11单元格范围内的第6个数据，即I7单元格的内容“68”。

方法二：使用VLOOKUP函数。

具体操作步骤如下：

2、动图演示如下。

3、公式解析。

（1）A2&B2：

我们都知道，VLOOKUP函数查找时查找值默认只能有一个条件，我们这里是多条件查找，所以可以通过文本连接符&将两个条件连接起来作为新的查找值。
新的查找值也就是“姓名学号”。

（2）IF({1,0},$G$2:$G$11&$H$2:$H$11,$I$2:$I$11)：

{1,0}相当于{TRUE,FALSE}。
所以该公式就有两种情况：第一种情况：=IF(1,$G$2:$G$11&$H$2:$H$11,$I$2:$I$11)，这种情况返回G2:G11单元格区域内容和H2:H11单元格区域内容合并后的结果。
第二种情况：=IF(0,$G$2:$G$11&$H$2:$H$11,$I$2:$I$11)，这种情况返回I2:I11单元格区域内容。
所以{1,0}相当于重新构建了两列数据，第1列数据是以G2:G11单元格区域内容和H2:H11单元格区域内容合并后的数据，第2列数据是I2:I11单元格区域构建的数据，如下图所示。

（3）=VLOOKUP(A2&B2,IF({1,0},$G$2:$G$11&$H$2:$H$11,$I$2:$I$11),2,0)：

第一个参数查找值“A2&B2”，要返回的结果值在IF构建的新数据区域中，属于第2列，所以第3个参数为2，这里是精确查找，所以第4个参数为0或者FALSE。

方法三：使用OFFSET函数+MATCH函数。

具体操作步骤如下：

2、动图演示如下。

3、公式解析。

（1）MATCH(A2&B2,$G$2:$G$11&$H$2:$H$11,0)：

将A2单元格的姓名与B2单元格的学号通过用文本连接符合并作为新的查找内容，将G列的姓名和H列的学号通过文本连接符合并作为新的查找区域，0表示精确查找。
该公式返回的结果为“6”。

（2）=OFFSET($I$1,MATCH(A2&B2,$G$2:$G$11&$H$2:$H$11,0),)：

OFFSET函数是指以指定的单元格引用为参照系，通过给定偏移量得到新的引用。
返回对单元格或单元格区域中指定行数和列数的区域的引用。
返回的引用可以是单个单元格或单元格区域。
可以指定要返回的行数和列数。
该公式表示以$I$1为参照单元格，通过MATCH查找出来顺序作为向下偏移的行数，偏移列数量省略表示不偏移，第三个、第四个参数省略表示只返回一个单元格区域。
第（1）步MATCH函数得到的结果为6，所以向下偏移6行时找到“68”。

方法四：使用SUM函数。

具体操作步骤如下：

2、动图演示如下。

3、公式解析。

（1）A2=$G$2:$G$11：

判断A2单元格的内容是否与G2:G11单元格区域的内容相等，如果相等，返回TRUE，否则，返回FALSE。
该公式返回一个逻辑值数组{FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE}。

（2）B2=$H$2:$H$11：

判断B2单元格的内容是否与H2:H11单元格区域的内容相等，如果相等，返回TRUE，否则，返回FALSE。
该公式返回一个逻辑值数组{FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE}。

（3）=SUM((A2=$G$2:$G$11)(B2=$H$2:$H$11)($I$2:$I$11))：

3个值相乘，只有当前面2个值都为TRUE时，最后的结果才为TRUE，而TRUE=1，FALSE=0，从上面2步中，我们可以看到只有第6个值为TRUE，其余都为FALSE。
再与I2:I11的值相乘，最后通过SUM函数将得到的值相加起来，最后的结果“68”，将公式往下填充，即可得到其他单元格的值。

方法五：使用SUMPRODUCT函数。

具体操作步骤如下：

2、动图演示如下。

3、公式解析。

（1）A2=$G$2:$G$11：

判断A2单元格的内容是否与G2:G11单元格区域的内容相等，如果相等，返回TRUE，否则，返回FALSE。
该公式返回一个逻辑值数组{FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE}。

（2）B2=$H$2:$H$11：

判断B2单元格的内容是否与H2:H11单元格区域的内容相等，如果相等，返回TRUE，否则，返回FALSE。
该公式返回一个逻辑值数组{FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE}。

（3）=SUMPRODUCT((A2=$G$2:$G$11)(B2=$H$2:$H$11)($I$2:$I$11))：

3个值相乘，只有当前面2个值都为TRUE时，最后的结果才为TRUE，而TRUE=1，FALSE=0，从上面2步中，我们可以看到只有第6个值为TRUE，其余都为FALSE。
再与I2:I11的值相乘，最后通过SUMPRODUCT函数将每个数组对应元素的值相乘，最后再相加，得到的结果为“68”，将公式往下填充，即可得到其他单元格的值。

方法六：使用MAX函数。

具体操作步骤如下：

2、动图演示如下。

3、公式解析。

（1）A2=$G$2:$G$11：

判断A2单元格的内容是否与G2:G11单元格区域的内容相等，如果相等，返回TRUE，否则，返回FALSE。
该公式返回一个逻辑值数组{FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE}。

（2）B2=$H$2:$H$11：

判断B2单元格的内容是否与H2:H11单元格区域的内容相等，如果相等，返回TRUE，否则，返回FALSE。
该公式返回一个逻辑值数组{FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE}。

（3）=MAX((A2=$G$2:$G$11)(B2=$H$2:$H$11)($I$2:$I$11))：

将(A2=$G$2:$G$11)(B2=$H$2:$H$11)($I$2:$I$11)这3个数组相乘，最后得到的结果为{0;0;0;0;0;68;0;0;0;0}，根据MAX函数的原理，找到一组值中的最大值并返回，很显然，结果数组中的最大值为“68”，所以返回的结果为68，将公式往下填充，即可得到其他单元格的值。

方法七：使用MIN函数+IF函数。

2、动图演示如下。

3、公式解析。

（1）(A2=$G$2:$G$11)(B2=$H$2:$H$11)：

判断A2单元格的内容是否与G2:G11单元格区域的内容相等，如果相等，返回TRUE，否则，返回FALSE。
判断B2单元格的内容是否与H2:H11单元格区域的内容相等，如果相等，返回TRUE，否则，返回FALSE。
该公式返回一个0和1组成的数组{0;0;0;0;0;1;0;0;0;0}。

（2）IF((A2=$G$2:$G$11)(B2=$H$2:$H$11),$I$2:$I$11)：

如果(A2=$G$2:$G$11)(B2=$H$2:$H$11)为TRUE，返回$I$2:$I$11单元格区域的内容，如果为FALSE，返回空。
所以该公式返回的结果为{FALSE;FALSE;FALSE;FALSE;FALSE;68;FALSE;FALSE;FALSE;FALSE}。

（3）=MIN(IF((A2=$G$2:$G$11)(B2=$H$2:$H$11),$I$2:$I$11))：

MIN函数是返回一组值中的最小值， 空单元格、逻辑值和文本将被忽略。
由第（2）步可知，只有68是数值，所以返回的结果就是68，将公式往下填充，即可得到其他单元格的值。

方法八：使用INDEX函数+MATCH函数。

2、动图演示如下。

3、公式解析。

（1）$I$2:$I$11：

要返回的结果所在的单元格区域。
该公式得到一组数组{80;71;82;84;70;68;90;74;70;89}。

（2）MATCH(A2&B2,$G$2:$G$11&$H$2:$H$11,0)：

将A2单元格的姓名与B2单元格的学号通过用文本连接符合并作为新的查找内容，将G列的姓名和H列的学号通过文本连接符合并作为新的查找区域，0表示精确查找。
该公式返回的结果为“6”。

（3）=INDEX($I$2:$I$11,MATCH(A2&B2,$G$2:$G$11&$H$2:$H$11,0))：

INDEX函数的作用是： 返回表或区域中的值或值的引用。
上述公式由第（1）步和第（2）步得到的结果，可将公式写成=INDEX({80;71;82;84;70;68;90;74;70;89},6)。
6是行号，也就是从在{80;71;82;84;70;68;90;74;70;89}这组值中返回第6行单元格值的引用，所以为68，将公式往下填充，即可得到其他单元格的值。