Excel VLookUp函數的使用方法，含逆向查找、一對多查找和近似匹配實例

Vlookup函數是 excel 中用于按行查找指定值的函數；它至少要有三個參數，第四個參數為可選項，用于確定匹配選項（精確匹配或近似匹配），如果省略，默認為近似匹配。Vlookup函數涉及的查找情況比較多，如果設置不正確可能返回多種錯誤值。

VLookUp函數的使用方法特別多，本篇將先介紹它的語法、基本使用方法和常用方法，其中基本使用方法介紹了 6 個實例，常用方法介紹了 3 個實例，實例包含多種返回錯誤值、在查找值中使用通配符問題（?）和星號（*）、逆向查找、近似匹配和一對多查找實例。

一、excel VLookUp函數語法

1、表達式：VLOOKUP(LookUp_Value, Table_Array, Col_Index_Num, [Range_LookUp])

中文表達式：VLOOKUP(查找值, 查找區域, 返回列號, [匹配選項])

2、說明

（1）、Col_Index_Num 以指定區域左邊一列為第一列，例如指定區域為 B2:D8，則以 B 列為第一列，若把 Col_Index_Num 設置為 2，則返回 C 列的值。

（2）Range_LookUp 為可選參數，它共有兩個選項，一個是 True，另一個是 False，True 為近似匹配，False 為精確匹配；如果省略 Range_LookUp，默認選擇 True。

（3）常見返回錯誤

A、如果把 Range_LookUp 設置為 True（近似匹配），需要對第一列排序，否則可能返回錯誤值；如果把 Range_LookUp 設置為 True，LookUp_Value 比 Table_Array 中第一列的最小值小，將返回錯誤值 #N/A；如果把 Range_LookUp 設置為 False，若返回錯誤值 #N/A，表示Table_Array 中沒有查找值。

B、如果 Col_Index_Num 大于 Table_Array 的列數，將返回引用錯誤值 #REF!。

C、如果 Table_Array 小于 1，將返回值錯誤 #VALUE!。

D、如果公式缺少引號，通常返回名稱錯誤值 #NAME?。

（4）查找數字或日期時，不能把選中區域第一列設置為文本，否則可能返回不正確的值。

（5）如果把 Range_LookUp 設置為 False（精確匹配）且 LookUp_Value 為文本，則可在 LookUp_Value 中使用通配符問號（?）和星號（*）；問號表示任意一個字符，星號表示一個或多個字符；如果要查找問號或星號，需要在它們前面加轉義字符 ~，例如查找問題，應該這樣表示 ~?。

（6）在 Table_Array 中不能有前后空格、單雙引號和非打印字符，如果有這些字符，應該用函數或替換的方法把它們去掉，否則可能返回意外值。

二、Excel VLookUp函數的使用方法及實例

（一）省略參數 Range_LookUp 的實例

1、假如要查找“粉紅襯衫”的“銷量”。選中 A11 單元格，把公式 =VLOOKUP(A11,B2:E8,4,) 復制到 B11，按回車，返回查找結果 892；操作過程步驟，如圖1所示：

圖1

2、公式說明：

A、A11 是公式 =VLOOKUP(A11,B2:E8,4,) 中的查找值，B2:E8 為查找區域，4 為返回列號（即以 B 列為第一列，返回第四列，即 E 列的值），公式省略了第四個參數 Range_LookUp，默認選擇 True（近似匹配）；公式的意思是：在 B2:E8 這片單元格區域中查找 A11，在 B6 找到后，返回 B6 在 E 列的對應值 E6，即返回 892。

B、如果省略第四個參數 Range_LookUp，第三個參數后既可省略逗號（,）又可加上逗號（,）。

（二）把參數 Range_LookUp 設置為 True 需要對第一列排序和 LookUp_Value 小于第一列最小值返回 #N/A 的實例

1、雙擊 B11 單元格，把公式 =VLOOKUP(A11,D2:E8,2,TRUE) 復制到 B11，按回車，返回錯誤值 #N/A；框選 D2:D8，選擇“數據”選項卡，單擊“升序”圖標，打開“排序提醒”窗口，選擇“擴展選定區域”，單擊“確定”，則每行按“價格”“升序”按序，B11 中的值隨即變為 892；選中 A11 單元格，輸入 36，單擊一下 B11，則 B11 中的值變為 錯誤值 #N/A；操作過程步驟，如圖2所示：

圖2

2、公式說明：

A、公式 =VLOOKUP(A11,D2:E8,2,TRUE) 把參數 Range_LookUp 設置為 True，未對“價格”排序之前，返回錯誤值 #N/A（A11 中的 38.8 是 D 列中的最小值），對“價格”按“升序”排序后，能返回正確的查找值 892，說明把參數 Range_LookUp 設置為近似匹配時，需要對選中區域的第一列排序才能確保返回正確的查找值。

B、即使對第一列排序，如果查找值（36）小于第一列最小值（38.8），同樣會返回錯誤值 #N/A。

（三）把參數 Range_LookUp 設置為 False 返回 #N/A 的實例

1、雙擊 B11 單元格，把公式 =VLOOKUP(A11,D2:E8,2,FALSE) 復制到 B11，按回車，返回錯誤值 #N/A；操作過程步驟，如圖3所示：

圖3

2、把參數 Range_LookUp 設置為 False（精確匹配）時，不管有沒有對選中區域的第一列排序，如果沒有找到值，都返回錯誤值 #N/A。

（四）公式缺少引號返回名稱錯誤值 #NAME? 實例

1、雙擊 B11 單元格，把公式 =VLOOKUP(“黑色T恤”,B2:E8,4,FALSE) 復制到 B11，按回車，返回名稱錯誤值 #NAME?；雙擊 B11，把公式中的全角雙引號改為半角雙引號，按回車，返回查找值 982；操作過程步驟，如圖4所示：

圖4

2、全角雙引號不能用于把公式的文本括起來，如果查找文本中有全角雙引號，外面還要加半角雙引號，例如 =VLOOKUP("“黑色T恤”",B2:E8,4,FALSE)。

（五）在查找值 LookUp_Value 中使用通配符問號（?）或星號（*）的實例

（1）在查找值中使用通配符問號（?）

1、假如查找“產品名稱”以“粉紅”開頭且后面只有兩個字的銷量和查找 ? 對應的銷量。雙擊 B11 單元格，把公式 =VLOOKUP(A11,B2:E8,4,) 復制到 B11，按回車，返回查找結果 892；雙擊 A11 單元格，把內容改為 ~?，單擊一下 B11，B11 中的數值變為 982；操作過程步驟，如圖5所示：

圖5

2、說明：

A、公式 =VLOOKUP(A11,B2:E8,4,) 中 A11（粉紅??）表示以“粉紅”開頭且后面只有兩個字的文本，它正好是“粉紅襯衫”，B3 和 B6 的服裝名稱也以“粉紅”開頭，但“粉紅”后不止兩個字，所以不滿足條件。

B、把 A11 中的內容改為 ~?，表示要查找問號，恰好返回 ? 對應的銷量 982。Excel 中 ~ 顯示為 - 是字體原因，換種字體（如宋體）即顯示正常。

（2）在查找值中使用通配符星號（*）

1、假如查找“產品名稱”以“粉紅”開頭的、以“T恤”結尾和以“白色”開頭且以“襯衫”結尾的銷量。雙擊 B10 單元格，把公式 =VLOOKUP("粉紅*",B2:E8,4,) 復制到 B10，按回車，返回查找結果 329；雙擊 B10，把查找值 "粉紅*" 改為 "*T恤"，按回車，返回查找結果 638；雙擊 B10，把查找值 "*T恤" 改為 "白色*襯衫"，按回車，返回查找結果 897；操作過程步驟，如圖6所示：

圖6

2、說明：

公式 =VLOOKUP("粉紅*",B2:E8,4,) 中 "粉紅*" 表示查找以“粉紅”開頭的服裝、*T恤" 表示查找以任意字符開頭且以“T恤”結尾的服裝、白色*襯衫"表示查找以“白色”開頭且以“襯衫”結尾的服裝。

（六）參數 Table_Array 中有前后空格的實例

1、假如要查找 A11 中的“綠色T恤”。雙擊 B11 單元格，把公式 =VLOOKUP(A11,B2:E8,4,) 復制到 B11，按回車，返回錯誤值 #N/A；雙擊 B11，把公式改為 =VLOOKUP(A11,TRIM(B2:E8),4,)，按 Ctrl + Shift + 回車，返回查找值 528；操作過程步驟，如圖7所示：

圖7

2、公式說明：

A、B4 中的內容就是“綠色T恤”，但公式 =VLOOKUP(A11,B2:E8,4,) 返回錯誤值 #N/A，而加去掉文本前后空格的函數 Trim 后，能返回正確的值，說明 B4 中有空格。

B、=VLOOKUP(A11,TRIM(B2:E8),4,) 是數組公式，所以需要按 Ctrl + Shift + 回車，因為 B2:E8 以數組形式返回 B2:E8 中的所有值，然后用Trim函數逐個把空格去掉。

三、Excel VLookUp函數的使用方法常用實例

（一）近似匹配

1、假如要根據平均分查找對應學生的評定。選中 J9 單元格，輸入 =a4，按回車，返回“黃月語”；雙擊 K9，把公式 =VLOOKUP(H4,J3:K6,2) 復制到 K9，按回車，返回“黃月語”的評定“良”；操作過程步驟，如圖8所示：

圖8

2、公式說明：

A、公式 =VLOOKUP(H4,J3:K6,2) 意思是在 J3:K6（評定表）中查找“黃月語”的平均分（H4）對應的評定，由于 H4 中的數字為 86，評定表中并沒有這個值，因此只能用近似匹配（即省略參數 Range_LookUp 或把它設置為 True），從而選出 80 到 89 分段的評定。

B、提示：評定表中的分數必須按“升序”排序，否則可能返回不正確的值。

（二）逆向查找

1、假如要根據“產品名稱”查找對應的“編號”。雙擊 B11 單元格，把公式 =VLOOKUP(A11,CHOOSE({2,1},A2:A8,B2:B8),2) 復制到 B11，按回車，返回“編號”NS-286；操作過程步驟，如圖9所示：

圖9

2、公式 =VLOOKUP(A11,CHOOSE({2,1},A2:A8,B2:B8),2) 說明：

A、{2,1} 是Choose函數數組形式的用于指定返回哪個值的索引號，A2:A8 和 B2:B8 是返回值，數組中的 2 代表 A2:A8 中的值，1 代表 B2:B8 中的值；執行時，首先從數組中取出 2，然后從 B2:B8 中取出 B2（即“白色純棉T恤”）和從 A2:A8 中取出 A2（即“WS-580”），最后把這兩個元素組成數組第一行的元素，即 "白色純棉T恤","WS-580";；第二次從 B2:B8 中取出 B3（即“粉紅長袖襯衫”）和從 A2:A8 中取出 A3（即“WS-560”），組成數組第二個元素 "粉紅長袖襯衫","WS-560";；最后返回數組 {"白色純棉T恤","WS-580";"粉紅長袖襯衫","WS-560";"綠色T恤","WS-585";"粉紅襯衫","WS-581";"粉紅短袖襯衫","WS-561";"黑色T恤","NS-286";"白色長袖襯衫","NS-832"}，這樣就把“產品名稱”放到了“編號”的前面。

B、則公式變為  =VLOOKUP(A11,{"白色純棉T恤","WS-580";"粉紅長袖襯衫","WS-560";"綠色T恤","WS-585";"粉紅襯衫","WS-581";"粉紅短袖襯衫","WS-561";黑色T恤","NS-286";"白色長袖襯衫","NS-832"},2)，找到 A11（黑色T恤）后，返回第 2 列，恰好是 NS-286。

（三）一對多查找

1、假如要查找指定“部門”（銷售部）的所有員工姓名。雙擊 F2 單元格，輸入部門“銷售部”，雙擊 A2，把公式 =(C2=$F)+A1 復制到 A2，按回車，返回 1，選中 A2，單擊 A2 右下角的單元格填充柄，則 A3 到 A8 全用相應數字填充；雙擊 G2，把公式 =IFERROR(VLOOKUP(ROW(A1),A1:C,2,0),"") 復制到 G2，按回車，返回查找結果“林語彤”，選中 G2，把鼠標移到 G2 右下角的單元格填充柄上，按住左鍵，往下拖，一直拖到最后一行，則返回“銷售部”的所有員工；再雙擊 F2，把“銷售”改為“財務”，按回車，返回所有“財務部”的員工姓名；操作過程步驟，如圖10所示：

圖10

2、公式說明：

（1）=(C2=$F)+A1

A、公式 =(C2=$F)+A1 用于把屬于同一部門的員工進行遞增編號，例如屬于“銷售部”的三個員工被編號為 1、2、3，屬于“財務部”的兩個員工被編號為 2、3。

B、C2 表示對列和行都是相對引用，往下拖時，會變為 C3、C4、……；往右拖時，會變為 D2、D3、……。$F 表示對列和行都是絕對引用，往下或往右拖時，F2 不會變。

C、C2=$F$2 是一個條件，即 C2 如果等于 F2，則返回 True，否則返回 False，C2 和 F2 都為“銷售部”，所以返回 True。則公式 =(C2=$F$2)+A1 變 =TRUE+A1，計算時 TRUE 被轉為 1，A1 為空，默認返回 0，所以 =TRUE+A1 返回 1。

D、往下拖到 A3，C2 變為 C3，A1 變為 A2，所以 A3 的公式為 =(C3=$F$2)+A2；C3 的值為“行政部”，不等于 F2，所以返回 False，A2 的值為 1，因此公式變為 =False+1，計算時由于 False 被轉為 0，所以 =False+1 返回 1。

（2）=IFERROR(VLOOKUP(ROW(A1),A1:C$8,2,0),"")

A、ROW(A1) 返回 A1 的行號 1；A1:C$8 表示 A1 到 C8 這片單元格區域，往下拖時，A1 會變為 A2、A3、……，C$8 則不會變；公式在 G2 時，A1:C$8 為 A1:C8；公式在 G3 時，A1:C$8 為 A2:C8，其它的以此類推。

B、公式在 G2 時，VLOOKUP(ROW(A1),A1:C$8,2,0) 變為 VLOOKUP(1,A1:C8,2,0)，即在 A1:C8 中查找 1，找到的是 A2 中的 1，然后返回第 2 列對應的“林語彤”；第 4 個參數 0 表示近似匹配。

C、公式在 G3 時，VLOOKUP(ROW(A1),A1:C$8,2,0) 變為 VLOOKUP(ROW(A2),A2:C$8,2,0)，即 VLOOKUP(2,A2:C8,2,0)，查找值變為 2 且 A1 被排除查找范圍，在 A4 中找到 2，返回第 2 列對應的“黃子辛”；公式在 G4 時，查找值變為 3，A2 被排除查找范圍，其它的以此類推。

D、IFERROR 用于返回錯誤值時的處理，如果 VLOOKUP(ROW(A1),A1:C$8,2,0) 返回錯誤值，則返回空，否則返回 VLOOKUP(ROW(A1),A1:C$8,2,0) 的返回值。

除以上VLookUp函數的使用方法外，VLookUp函數還可以用If函數組合多條件查找、查找重復項、與Match函數組合查找等，這些內容將在后續文章中介紹。

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。