工作必備vlookup函數3大用法，讓你效率再翻倍！

06-23

這麼多的用法，你經常碰到哪幾種？據說那個查找日期的很少有人注意：

一、基本用法

1、根據貨號精確查找信息

=VLOOKUP($C45,$C$1:$G$8,COLUMN(B1),0)

註：需要注意的是查找值的引用，Colum(b1)的結果是2，當公式向右複製時可以生成3，4，5，..

2、模糊查找

模糊查找產品名稱數量

=VLOOKUP("*"&E2&"*",$A$2:$C$10,3,)

註：*是通配符，代表任意多個字元。可參考VLOOKUP常見錯誤解決辦法

3、逆向查找

=VLOOKUP(A11,IF(,B2:B7,A2:A7),2,0)

註：個人覺得使用Index+Match函數組合更方便

=INDEX(A2:A7,MATCH(A11,B2:B7,0))

二、進階用法

1、多條件查詢

多對一查找

=VLOOKUP(E2&F2,IF(,A2:A11&B2:B11,C2:C11),2,0)

註：數組公式輸完後需要按CTRL+SHIFT+ENTER三鍵結束。

一對多查詢

先創建一個輔助列，在B2單元格中輸入公式=C2&COUNTIF($C$2:C2,C2)，統計該區域要查找值個數並做標記

在G2單元格中輸入公式

=IFERROR(VLOOKUP($G$1&ROW(1:1),$B$2:$E$14,COLUMN(B:B),0),"")，回車

2、區間模糊查找

查找稅率

在A11中輸入公式=VLOOKUP(SUBSTITUTE(A11,"~","~~"),A2:D8,3,0)

註：~用於查找通配符，如果在vlookup公式中出現，會被認為特定用途，非真正的~

計算個人所得稅先創建輔助列，在G2中輸入公式

=(F2-3500)*VLOOKUP(F2-3500,$B$2:$D$8,2)-VLOOKUP(F2-3500,$B$2:$D$8,3)

3、求和

在D57中輸入公式

=SUM(VLOOKUP(T(IF(,$B$53:$B$55)),$F$53:$G$55,2,0)*D53:D55)

註：數組公式輸完後需要按CTRL+SHIFT+ENTER三鍵結束。

4、盤點對賬（帶通配符）

在D15中輸入公式

=TEXT(VLOOKUP(SUBSTITUTE(A15,"*","~*"),$E$15:$F$17,2,)-C15,"少0;多0;")

註：TEXT可以設置數字顯示的格式

5、查找符合條件的值

在F10中輸入公式

=VLOOKUP(1=1,IF(,$B$3:$B$7>500,$B$3:$B$7),2,0)

在F3中輸入公式

=VLOOKUP(MAX(($A$3:$A$7=$E$3)*ROW(1:5)),IF(,ROW(1:5),$B$3:$B$7),2,)

輸完以上公式按CTRL+SHIFT+ENTER三鍵結束。

三、特殊用法

1、提取電話號碼公式=VLOOKUP(,MID(A2,ROW($1:$14),11)*,2,)

提取 / 間隔的最後一段內容

=VLOOKUP("/*",RIGHT(A8,ROW($1:$12)-),2,)

提取金額

=VLOOKUP(9^9,MID(A14,MIN(IF(ISNUMBER(-MID(A14,ROW($1:$14),1)),ROW($1:$14))),ROW($1:$14))*1,1)

輸完以上公式按CTRL+SHIFT+ENTER三鍵結束。

2、排列成相同的順序

輸入公式= IFERROR(VLOOKUP($B2,$B$11:$D$13,COLUMN(A1),0),"")

3、製作工資條

輸入公式=VLOOKUP($A10,$A$3:$M$6,COLUMN(B1),0)，向右填充。對於雙行表頭製作工資條，拿走，不謝！

4、日期查找

輸入公式

=TEXT(IFERROR(VLOOKUP($F30,$A$30:$D$35,4,0),0),"yyyy-m-d;;")