万万没想到，VLOOKUP还可以这么用

Vlookup作为Excel中匹配函数一哥，使用频率之高，日常工作中我们基本只用到它的查找匹配功能。

下面这两种情况，可以说90%的人都没用过....

它居然可以当成 提取函数和拆分函数来使用。

1、提取数字

下图中，A列信息中，一个员工存在多个编号，需要提取员工的最新编号，也就是最后面出现的8位数字编号。

B2单元格输入公式：

然后Ctrl+shift+enter三键齐按，完成数组公式的输入，向下填充完成编号的提取。

公式解读：

我们先来看vlookup函数的参数二，查找区域，它是一长串公式：

MID函数是一个字符串提取函数：

语法：MID(要提取的字符串，开始位置，提取长度)

这里要提取的字符串是 A2&"s" ，这里将A2单元格内容加上一个"s"，是为了防止以数字结尾、影响vlookup近似匹配的机制（后面进一步解释）

开始的位置从1到100，提取长度为8位（编号长度），公式提取的内容如下所示(未展示完全)：

是一个一行两列的数组，接着将上面的结果与这个数组运算，得到如下两列内容：

可以发现的是，非数字与0或者1相乘返回“#VALUE!”，数字与0或者1相乘返回0或数字本身；

也就是说只有完整的8位数字，才会有值，这样我们就把A2单元格中完整的8位数字提取了出来。

接着利用Vlookup函数在上面的内容中查找数字0，由于使用的是近似匹配，函数会一直向下查找到最后一个0，也就是末尾出现的员工编号。

为啥要在A2后面加一个s？

回到最上面的话题，如果要提取的字符串最后是以数字结尾，比如9，那MID函数最后一个内容是9，与运算，结果是，vlookup函数的近似匹配最终返回9，无法得到正确值。

2、拆分数字

A列包含6个数字，有正数也有负数，现在需要将每个数字单独提取出来，分别放置于后面的6列内，如下图所示：

B2单元格输入公式：

然后Ctrl+shift+enter三键齐按，完成数组公式的输入，向右向下填充完成数字的提取。

公式解读：

先看B2单元格：

1、COUNTIF($A2:A2,"

2、COLUMN(A1)+COUNTIF($A2:A2,"

3、MID($A2,COLUMN(A1)+COUNTIF($A2:A2,")，表示分别取长度1和2，返回一维数组 {"2";"2-"}

4、MID($A2,COLUMN(A1)+COUNTIF($A2:A2,"* ，前面的{"2";"2-"}**返回二维数组

5、最后利用vlookup函数匹配0，返回数字2。

公式向右拖动到C2单元格：

1、COUNTIF($A2:B2,"

2、COLUMN(B1)+COUNTIF($A2:B2,"

3、MID($A2,COLUMN(B1)+COUNTIF($A2:B2,"

4、MID($A2,COLUMN(B1)+COUNTIF($A2:B2,"

5、最后利用vlookup函数匹配0，返回数字-5。

公式向右拖动到D2单元格：

1、COUNTIF($A2:C2,"

2、COLUMN(C1)+1返回 4

3、返回{"-";"-8"}

4、返回{#VALUE!,#VALUE!;0,-8}

5、最后利用vlookup函数匹配0，返回数字-8。

以此类推提取所有的正数和负数。

小结

以上两种方法皆是利用数组公式构建vlookup函数的匹配区域，原理大同小异，大家可以手动尝试下。