9个查找匹配相关的Excel函数公式，1对1，1对多，多对多

说起查找引用，想必大家也很熟悉了，Excel里也自带了许多查找引用的函数，今天就来给大家系统讲讲查找引用的相关公式。

“查找”是使用EXCEL过程中是非常普遍的工作，所以EXCEL设计了很多各式各样的“查询与引用类函数”

今天就来给大家分享几种常见的“查找引用”情况和一些经典的嵌套函数、数组函数。

1.

一对一查询

一个匹配条件，对应一个匹配值。

这是最常见、最简单的条件匹配，同时也是解法最多的一种。

我们给出两种“快准狠”的方案，供大家选择：

1）=VLOOKUP($E$2,$A$1:$B$16,2,0)，没什么可讲的了，VLOOKUP函数的基础用法。

2）=INDEX($B$2:$B$16,MATCH($E$2,$A$2:$A$16,0))

这个就是经常说到的INDEX+MATCH的经典组合了。通过MATCH函数查到匹配条件在A列中的位置序号，返回给INDEX函数作为第二参数，引出B列的内容。

一对一查找，大家记这两个就好了，其它的函数解法拿来学习学习思路即可。

2.

一对多查询

在匹配条件列中存在，但不局限于唯一性，导致有多个匹配值存在。

这是一个经典用法“万金油”函数，这个函数的别称充分说明它的应用环境很广，同时也决定了它在写法上的变化很多。

我们列几个写法吧：

1）匹配条件对应字段2中的最大值

{=MAX(IF($A$2:$A$16=$E$2,$B$2:$B$16,""))}，此函数返回13。

2）匹配条件第二次出现时对应字段2的值

{=INDEX($B$2:$B$16,SMALL(IF($A$2:$A$16=$E$2,ROW($1:$15),99^9),2))}，此函数返回3。

3）列出所有匹配条件的值

{=IFERROR(INDEX($B$2:$B$16,SMALL(IF($A$2:$A$16=$E$2,ROW($1:$15),99^9),ROW(E1))),"")}，下拉填充单元格，最后形成的效果如下图：

通过上面3个案例，我们可以看到“万金油”函数的几个共同点：

a、键入公式后，都需要按CTRL+SHIFT+ENTER三键结束的数组函数；

b、都是围绕IF函数的判断，形成新的数列，再使用SMALL或者LARGE函数来确定我们需要的序号，即可返回给INDEX函数索引出对应的匹配值。

3.

多对一查找

多个匹配条件确定一个匹配值的情况

两列条件决定一个匹配值，我们可以使用下列函数来解决。

公式一：

=SUMPRODUCT(($A$2:$A$16=$F$2)*($B$2:$B$16=$F$3),$C$2:$C$16)

SUMPRODUCT是多条件匹配用的最多，而且写法最简单的写法，语法如下：

=SUMPRODUCT((【条件1区域】比较符【条件1】)*(【条件2区域】比较符【条件2】)*（【条件n区域】比较符【条件n】）,【匹配值列】)

公式二：

=LOOKUP(1,0/(($A$2:$A$16=$F$2)*($B$2:$B$16=$F$3)),($C$2:$C$16))，LOOKUP是利用了二分法运算原理做的函数思路，一定要注意条件需要用括号括起来（所有条件连乘之后，再用0除），语法如下：

=LOOKUP(1,0/((【条件1区域】比较符【条件1】)*(【条件2区域】比较符【条件2】)*（【条件n区域】比较符【条件n】）)，【匹配值列】)

上面的例子是多对一查找的在一维数据上的匹配方式，还有一种特殊情况需要大家注意。

如果是下图所示的二维数据，又该如何操作呢？

横纵交叉点匹配值，也属于多条件查询，我们此时还是使用INDEX+MATCH的经典嵌套函数解决：

=INDEX($B$19:$E$25,MATCH($H$18,$A$19:$A$25,0),MATCH($H$19,$B$18:$E$18,0))，用两个MATCH函数分别确定横纵匹配条件出现的序号，再返回给INDEX函数索引出横纵交叉点上的值。

4.

多对多查找

多个查找条件，匹配多个结果。

例如上图的案例，AN有两组对应值，我们需要匹配出所有的匹配值，可以使用下面的函数：

{=IFERROR(INDEX($C$2:$C$16,SMALL(IF(($A$2:$A$16=$F$2)*($B$2:$B$16=$F$3),ROW($1:$15),9^9),ROW(F1))),"")}