EXCEL VLOOKUP 函數(垂直查找函數)

  基礎篇

VLookUp中的 V 表示垂直方向,LookUp的漢語意思是「查找」,故整體意思為「垂直查找」。

VLookUp功能:您可以使用VLookUp函數搜尋某個單元格區域 (區域:工作表上的兩個或多個單元格。區域中的單元格可以相鄰或不相鄰。)的第一列,然後返回該區域相同行上任何單元格中的值(在EXCEL幫助文檔中的解釋)。

英文格式:VLookUp(lookup_value, table_array, col_index_num, [range_lookup])

中文格式:VLookUp(查找目標,查找範圍,返回值的列數,精確OR模糊查找)

lookup_value 必需,要在表格或區域的第一列中搜尋的值,lookup_value 參數可以是值或引用。

table_array 必需,包含數據的單元格區域,可以使用對區域(例如,A2:D8)或區域名稱的引用。

col_index_num 必需,table_array 參數中必須返回的匹配值的列號。

range_lookup 可選,一個邏輯值(即0或1,其中1為查找近似值,默認為1),指定希望 VLOOKUP 查找精確匹配值還是近似匹配值。

應用舉例:EXCEL VLOOKUP 函數(垂直查找函數)

如上圖,假如我們要查找南星的年齡,運用VLookUp 函數:

輸入第一個參數lookup_value(必需)的值為「南星」(理解為「要找誰的年齡‘);

輸入第二個參數table_array(必需)的值為」B3:D8「(理解為」在哪個區域找南星這個值「);

輸入第三個參數col_index_num(必需)的值為」3「(理解為「當找到南星所在行後,最終找南星的年齡所在列數」);

輸入第四個參數range_lookup(可選)的值為「0」(理解為「以上我要查找的內容要精確‘)

以上四個實例參數,常見問題解答:

(第一個參數)要查找的內容可以是一個值(如」南星「),也可以是一個單元格的引用(如南星這個值所在的單元格」A13「);

(第二個參數)這個查找的區域是否要包括項目行(即第2行),個人認為無影響,只要第一個參數在這個區域的第1列就行;

(第三個參數)為什麼是」3「而不是」D」,3表示的是這個區域從左往右數的第3列的意思。

(第四個參數)習慣上都填」0「,不默認。

初級篇

如何避免出現錯誤值?

EXCEL 2003 在VLookUp查找不到,就#N/A的錯誤值,我們可以利用錯誤處理函數把錯誤值轉換成0或空值,即:

=IF(ISERROR(VLookUp(參數略)),””,VLookUp(參數略))

EXCEL 2007、EXCEL 2010中提供了一個新函數IFERROR,處理起來比EXCEL2003簡單多了,即:

=IFERROR(VLOOKUP(參數略),””)

進階篇

如果我們需要查找包含「AAA」的產品名稱怎麼表示呢?如下圖表中所示。

EXCEL VLOOKUP 函數(垂直查找函數)

公式=VLOOKUP(“*”&A10&”*”,A2:B6,2,0)

公式說明:VLOOKUP的第一個參數允許使用通配符「*」來表示包含的意思,把*放在字符的兩邊,即”*” & 字符 & “*”。

模糊查找的兩個重要規則:

1、引用的數字區域一定要從小到大排序。雜亂的數字是無法準確查找到的。如下面A列符合模糊查找的前題,B列則不符合。

EXCEL VLOOKUP 函數(垂直查找函數)

2、模糊查找的原理是:給一定個數,它會找到和它最接近,但比它小的那個數。詳見下圖說明。

EXCEL VLOOKUP 函數(垂直查找函數)

高級篇

一、VLOOKUP的反向查找

舉例:要求在如下圖所示表中的姓名反查工號,如下圖

EXCEL VLOOKUP 函數(垂直查找函數)

公式=VLOOKUP(A9,IF({1,0},B2:B5,A2:A5),2,0)

公式剖析:

1、這裡其實不是VLOOKUP可以做到從右至右的查找,而是利用IF函數的數組效應把兩列換位重新組合後,再按正常的從左至右查找。

2、IF({1,0},B2:B5,A2:A5)這是本公式中最重要的組成部分。在EXCEL函數中使用數組時(前提時該函數的參數支持數組),返回的結果也會是一個數組。這裡1和0不是實際意義上的數字,而是1相關於TRUE,0相當於FALSE,當為1時,它會返回IF的第二個參數(B列),為0時返回第二個參數(A列)。根據數組運算返回數組,所以使用IF後的結果返回一個數組(非單元格區域):{“張一”,”A001″;”趙三”,”A002″;”楊五”,”A003″;”孫二”,”A004″}。

二、VLOOKUP函數的多條件查找

舉例:要求根據部門和姓名查找C列的加班時間,如下圖

EXCEL VLOOKUP 函數(垂直查找函數)

公式:{=VLOOKUP(A9&B9,IF({1,0},A2:A5&B2:B5,C2:C5),2,0)}

公式剖析:

1、A9&B9 把兩個條件連接在一起。把他們做為一個整體進行查找。

2、A2:A5&B2:B5,和條件連接相對應,把部分和姓名列也連接在一起,作為一個待查找的整體。

3、IF({1,0},A2:A5&B2:B5,C2:C5) 用IF({1,0}把連接後的兩列與C列數據合併成一個兩列的記憶體數組。按F9後可以查看的結果為:

{“銷售張一”,1;”銷售趙三”,5;”人事楊五”,3;”銷售趙三”,6}

4、完成了數組的重構後,接下來就是VLOOKUP的基本查找功能了,另外公式中含有多個數據與多個數據運算(A2:A5&B2:B5),,所以必須以數組形式輸入,即按ctrl+shift後按ENTER結束輸入。

三、VLOOKUP函數的批量查找

舉例:要求把如圖表中所有張一的消費金額全列出來,如下圖

EXCEL VLOOKUP 函數(垂直查找函數)

公式:{=VLOOKUP(B$9&ROW(A1),IF({1,0},$B$2:$B$6&COUNTIF(INDIRECT(“b2:b”&ROW($2:$6)),B$9),$C$2:$C$6),2,)}

公式剖析:

1、B$9&ROW(A1) 連接序號,公式向下復制時會變成B$9連接1,2,3

2、給所有的張一進行編號。要想生成編號,就需要生成一個不斷擴充的區域(INDIRECT(“b2:b”&ROW($2:$6)),然後在這個逐行擴充的區域內統計「張一」的個數,在連接上$B$2:$B$6後就可以對所有的張一進行編號了。

3、IF({1,0}把編號後的B列和C組重構成一個兩列數組。

★★★相關知識補充★★★

1、數組公式的輸入方法是將公式輸入後,按Ctrl+Shift+Enter,而不是直接按Enter;

2、函數 INDIRECT:返回由文本字符串指定的引用。此函數立即對引用進行計算,並顯示其內容。當需要更改公式中單元格的引用,而不更改公式本身,請使用函數 INDIRECT。