Vlookup作為Excel中匹配函數一哥，使用頻率之高，日常工作中我們基本隻用到它的查找匹配功能。

下面這兩種情況，可以說90%的人都沒用過....

它居然可以當成 提取函數和拆分函數來使用。

下圖中，A列信息中，一個員工存在多個編号，需要提取員工的最新編号，也就是最後面出現的8位數字編号。

B2單元格輸入公式：

=VLOOKUP(0,MID(A2&"s",ROW($1:$100),8)*{0,1},2,1)

然後Ctrl shift enter三鍵齊按，完成數組公式的輸入，向下填充完成編号的提取。

公式解讀：

我們先來看vlookup函數的參數二，查找區域，它是一長串公式：

MID(A2&"s",ROW($1:$100),8)*{0,1}

MID函數是一個字符串提取函數：

語法：MID(要提取的字符串，開始位置，提取長度)

這裡要提取的字符串是 A2&"s" ，這裡将A2單元格内容加上一個"s"，是為了防止以數字結尾、影響vlookup近似匹配的機制（後面進一步解釋）

開始的位置從1到100，提取長度為8位（編号長度），公式提取的内容如下所示(未展示完全)：

{0,1}是一個一行兩列的數組，接着将上面的結果與這個數組運算，得到如下兩列内容：

可以發現的是，非數字與0或者1相乘返回“#VALUE!”，數字與0或者1相乘返回0或數字本身；

也就是說隻有完整的8位數字，才會有值，這樣我們就把A2單元格中完整的8位數字提取了出來。

接着利用Vlookup函數在上面的内容中查找數字0，由于使用的是近似匹配，函數會一直向下查找到最後一個0，也就是末尾出現的員工編号。

為啥要在A2後面加一個s？

回到最上面的話題，如果要提取的字符串最後是以數字結尾，比如9，那MID函數最後一個内容是9，與{0,1}運算，結果是{0,9}，vlookup函數的近似匹配最終返回9，無法得到正确值。

A列包含6個數字，有正數也有負數，現在需要将每個數字單獨提取出來，分别放置于後面的6列内，如下圖所示：

B2單元格輸入公式：

=VLOOKUP(0,MID($A2,COLUMN(A1) COUNTIF($A2:A2,"<0"),{1;2})*{0,1},2,0)

然後Ctrl shift enter三鍵齊按，完成數組公式的輸入，向右向下填充完成數字的提取。

公式解讀：

先看B2單元格：

1、COUNTIF($A2:A2,"<0")計算小于0的個數，A2單元格非負數，這裡返回 0

2、COLUMN(A1) COUNTIF($A2:A2,"<0")返回 1

3、MID($A2,COLUMN(A1) COUNTIF($A2:A2,"<0"),{1;2})，表示分别取長度1和2，返回一維數組 {"2";"2-"}

4、MID($A2,COLUMN(A1) COUNTIF($A2:A2,"<0"),{1;2})*{0,1} ，前面的{"2";"2-"}**{0,1}返回二維數組{0,2;#VALUE!,#VALUE!}

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

公式向右拖動到C2單元格：

1、COUNTIF($A2:B2,"<0")，沒有負數，依然返回 0

2、COLUMN(B1) COUNTIF($A2:B2,"<0")返回 2

3、MID($A2,COLUMN(B1) COUNTIF($A2:B2,"<0"),{1;2})返回 {"-";"-5"}

4、MID($A2,COLUMN(B1) COUNTIF($A2:B2,"<0"),{1;2})*{0,1}返回 {#VALUE!,#VALUE!;0,-5}

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

公式向右拖動到D2單元格：

1、COUNTIF($A2:C2,"<0")，有一個負數(-5)，函數返回1

2、COLUMN(C1) 1返回 4

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

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

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

............

以此類推提取所有的正數和負數。

以上兩種方法皆是利用數組公式構建vlookup函數的匹配區域，原理大同小異，大家可以手動嘗試下。

以上就是今天的分享，希望對你有所幫助，我們下期見~

更多精彩资讯请关注tft每日頭條，我们将持续为您更新最新资讯!