excel匹配_Excel中的数据匹配和查找

在Excel中，提供了多个可以用来进行数据匹配和查找的函数：HLOOKUP、INDEX、LOOKUP、MATCH和VLOOKUP等。其中使用最广的就是VLOOKUP，这篇文章就从VLOOKUP开始，介绍几种常见的匹配和查找数据的方式。

文章分成两部分：

第一部分介绍，VLOOKUP的基本使用 和 替代方法；

第二部分介绍，VLOOKUP无法实现的一些功能：多列匹配，向左查询匹配，匹配时区分大小写，返回最后一个匹配的数据而不是第一个。

第二部分会使用到数组公式(有兴趣的可以先点击查看)。

VLOOKUP的基本使用

先看一个示例，在B2:D9区域有一张员工薪资表，G2单元格可以输入员工的First Name，希望可以在G3单元格查找出相应员工的具体薪资。

这时候可以使用VLOOKUP函数来进行查找匹配。

使用之前先简单回顾一下VLOOKUP函数语法：

VLOOKUP (value, table, col_index,[range_lookup])

参数：

• value -查找的值(需要在下面参数table的第一列中查找)。

• table - 查找的区域。

• col_index - 返回值的区域中的列号。

• range_lookup -(可选)近似匹配 (TRUE) 或完全匹配 (FALSE)，默认为近似匹配。

上面的示例中可以在G3单元格中输入如下公式：

=VLOOKUP(G2,B3:D9,3,FALSE)

G2是要查找的内容，B3:D9是查找区域(注意第一列一定是First Name)，3是Salary在查找区域的列号，False表示精确匹配。

替代方式

除了VLOOKUP之外，也可以使用其他函数来代替，比如INDEX和MATCH的函数组合。

下面公式和之前使用VLOOKUP的效果是一样的。

=INDEX(D3:D9,MATCH(G2,B3:B9,0))

其中MATCH(G2,B3:B9,0)返回John在B3:B9中的行数4，然后INDEX(D3:D9,4)返回D3:D9中的第4行数据。

MATCH函数的最后一个参数0表示精确匹配。

多列匹配

常见的匹配是一列匹配，如果将上面的示例修改一下，用户同时提供First Name和Last Name，并且根据这两个值来同时匹配Salary。这时候应该怎么实现？

一个简单的方式是建一个辅助列，将First Name和Last Name合并，然后使用之前的公式就可以了。

如果不希望使用辅助列，就必须用到数组公式，什么是数组公式可以参考之前的文章。

我们来分步解释一下如何实现。

第一步，先将G2和G3单元格的数据合并。

=G2&G3

第二步，查找合并后的数据在查找区域中处于第几行，这里要使用MATCH函数，公式输入之后需要同时按下CTRL + SHIFT + ENTER。

{=MATCH(G2&G3,B3:B9&C3:C9,0)}

解释：公式中B3:B9&C3:C9是一个数组，存在于Excel的内存中：

{  “EmilySmith”；  “JamesAnderson”;  “MiaClark”;  “JohnLewis”;  “JessicaWalker”;  “MIAReed”;  “RichardLopez”}

将G2&G3的结果“JohnLewis”在这个数组进行精确匹配，将返回结果4(数组中的第4个元素)。

第三步，使用INDEX返回查找到的行号中的具体数值

{=INDEX(D3:D9,MATCH(G2&G3,B3:B9&C3:C9,0))}

从意义上看，这个公式跟之前替代VLOOKUP的INDEX、MATCH组合公式是一样的。

向左查询

使用VLOOKUP函数时，函数本身的实现方式会带来一些不便，比如VLOOKUP始终时向右查询，被查找的数据必须在查找区域的第一列中。如果需要向左做查找该怎么办？

比如，需要在G列填写Product信息，但是ID不在查找区域的第一列。

这时候可以使用INDEX和MATCH的组合公式，然后将公式拖动到G列其他行：

=INDEX($A$3:$A$6,MATCH(F3,$C$3:$C$6,0))

注意公式中“$”的使用。

区分大小写

VLOOKUP函数本身不区分大小写。比如下面的例子，查找区域中有“Mia”和“MIA”，当G2中输入“MIA”时会精确匹配到“Mia”。

如果需要区分大小写，又该如何操作？

第一步，找到一个可以区分大小写的函数EXACT。

=EXACT(G2,B5)//False=EXACT(G2,B8)//True

第二步，使用MATCH函数查找出对应行号(注意这里使用数组公式)。

{=MATCH(TRUE,EXACT(G2,B3:B9),0)}

解释：

EXACT(G2,B3:B9)返回一个数组，并保存在excel内存中。

{FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE}

MATCH(TRUE,EXACT(G2,B3:B9),0)，将返回数组中第一个TRUE的位置6。

第三步，使用INDEX返回查找到的行号中的具体数值

{=INDEX(D3:D9,MATCH(TRUE,EXACT(G2,B3:B9),0))}

跟之前的操作类似，这里就不作解释了。

精确匹配最后一项

VLOOKUP中如果查询区域有多个可以匹配到的数据，那么将会返回第一个匹配的数据。

如果希望返回最后一个匹配的数据，又该怎么实现？

还是之前的例子，这里把“Mia”改成了“MIA”，查找区域有两个“MIA”(第5行和第8行)，正常VLOOKUP会返回第5行数据。

这里我们可以使用LOOKUP函数来实现，同时还可以了解LOOKUP函数本身的一些特性。

先看实现公式(不是数组公式)：

=LOOKUP(2,1/(B3:B9=G2),D3:D9)

解释：

B3:B9=G2返回一个数组:

{FALSE; FALSE; TRUE; FALSE; FALSE; TRUE; FALSE}

接下来使用1来除以这个这个数组，执行除法的时候“TRUE”表示1，“FALSE”表示0。

1/{0;0;1;0;0;1;0}

1除以1返回1，1除以0返回错误#DIV/0，所以最后返回的数组为：

{#DIV/0!;#DIV/0!;1;#DIV/0!;#DIV/0!;1;#DIV/0!}

现在我们来看最外面层LOOKUP的含义：

首先是在上述的数组中查找数字2，看上去很让人困惑，因为数组中只有1和错误，没有2。不要担心，其实我们就是用数字2来让查询扫描到数组的最一位。

LOOKUP函数会自动忽略数组中的错误，它会扫描数组中的所有1，当它达到数组末尾时，将会后退到最后一个有效值——最后一个1。

找到最后一个1的位置后，就可以从D3:D9中匹配相应的数值。

参考资料：

• https://www.excel-easy.com/examples/vlookup.html

• https://www.excel-easy.com/examples/case-sensitive-lookup.html

• https://www.excel-easy.com/examples/left-lookup.html

• https://www.excel-easy.com/examples/two-column-lookup.html

• https://www.exceltip.com/excel-formula-and-function/vlookup-to-find-last-matching-value.html

• https://exceljet.net/how-to-lookup-first-and-last-match

---

更多内容可关注公众号：