Excel教程下載和軟件下載中心,Microsoft技術社區聯盟成員,全球極具影響力的Excel門戶,Office技術培訓社區

VLOOKUP函數常用套路大全

2017-10-05  作者:ExcelHome  閱讀:

今天和大家來說說VLOOKUP的那些事兒,深入了解一下VLOOKUP函數的各種用法,看看這位大眾情人還藏著多少不為人知的秘密。

函數的語法為:
VLOOKUP(要找誰,在哪兒找,返回第幾列的內容,精確找還是近似找)

第一參數是要在表格或區域的第一列中查詢的值。
第二參數是需要查詢的單元格區域,這個區域中的首列必須要包含查詢值,否則公式將返回錯誤值。如果查詢區域中包含多個符合條件的查詢值,VLOOKUP函數只能返回第一個查找到的結果。
第三參數用于指定返回查詢區域中第幾列的值,該參數如果超出待查詢區域的總列數,VLOOKUP函數將返回錯誤值#REF!,如果小于1返回錯誤值#VALUE!。
第四參數決定函數的查找方式,如果為0或FASLE,用精確匹配方式,而且支持無序查找;如果為TRUE或被省略,則使用近似匹配方式,同時要求查詢區域的首列按升序排序。

1、常規查詢
如圖,需要從B~E的數據表中,根據H3單元格的姓名查詢對應的職務。
公式為:
=VLOOKUP(H3,C:E,3,0)

提示:
VLOOKUP函數第三參數中的列號,不能理解為工作表中實際的列號,而是指定要返回查詢區域中第幾列的值。

2、帶通配符的查詢
如圖,需要從B~E的數據表中,根據H3單元格的姓氏,查詢對應的姓名和部門。
公式為:
=VLOOKUP($H3&"*",$C:$E,COLUMN(A1),0)

提示:
通配符“*”表示任意多個字符,VLOOKUP函數第一參數使用$H3&"*",即在C列中查詢以H2單元格內容開頭的內容,并返回對應列的信息。

3、近似查詢
如圖,需要根據H~I的對照表,判斷D列成績對應的評議結果。
公式為:
=VLOOKUP(D2,H:I,2)

提示:
VLOOKUP函數第四參數被省略,在近似匹配模式下返回查詢值的精確匹配值或近似匹配值。如果找不到精確匹配值,則返回小于查詢值的最大值。
使用近似匹配時,查詢區域的首列必須按升序排序,否則無法得到正確的結果。

4、逆向查詢
如圖,需要從B~E的數據表中,根據H3單元格的部門,查詢對應的姓名。
公式為:
=VLOOKUP(H3,CHOOSE({1,2},D2:D11,C2:C11),2,0)

提示:
VLOOKUP函數的查詢值要求必須位于查詢區域中的首列,如果被查找值不在數據表的首列時,需要先將目標數據進行特殊的轉換。
CHOOSE函數第一參數使用常量數組{1,2},將查詢值所在的D2:D11和返回值所在的C2:C11整合成一個新的兩列多行的內存數組。
生成的內存數組符合VLOOKUP函數的查詢值必須處于數據區域中首列的要求。VLOOKUP函數以職務做查詢條件,在內存數組中查詢并返回對應的姓名信息,從而實現了逆向查詢的目的。

5、多條件查詢
如圖,需要從B~E的數據表中,根據H3單元格的部門和I3單元格的職務,查詢對應的姓名。
J3單元格公式為:
=VLOOKUP(H3&I3,IF({1,0},D2:D11&E2:E11,C2:C11),2,)

提示:
使用連接符“&”將部門和職務合并成新的字符串,以此作為VLOOKUP函數的查詢條件。
IF部分,先將D列的部門和E列的職務進行連接,再使用IF({1,0}的方式,構造出部門職務在前、姓名在后的內存數組。
VLOOKUP函數在IF函數構造出的內存數組首列中查詢部門職務字符串的位置,返回對應的姓名。
數組公式,不要忘了按<Ctrl+Shift+Enter>組合鍵。

6、一對多查詢
如圖,需要從B~E的數據表中,根據H3單元格的職務,查詢對應的多個姓名。

首先在A2單元格輸入以下公式,向下復制:
=(E2=$H$3)+A1

然后在I3單元格輸入以下公式,向下復制:
=IFERROR(VLOOKUP(ROW(A1),A:C,3,0),"")

提示:
C列的職務每重復出現一次,A列的序號增加1。
VLOOKUP函數使用1至N的遞增序列作為查詢值,使用A:C列作為查詢區域,以精確匹配的方式返回與之相對應的B列的姓名。注意查找區域必須由輔助列A列開始。
最后將輔助列字體設置為白色或進行隱藏即可。

7、按指定次數重復數據
如圖,需要根據C列指定的次數,重復顯示B列的內容。

首先在A2單元格輸入以下公式,向下復制:
=A1+C2

然后在E2單元格輸入以下公式,向下復制:
=IFERROR(VLOOKUP(ROW(A1),A:B,2,0),E3)&""

轉載請注明來源"ExcelHome"并保留原文鏈接。
固定鏈接:http://www.fvtgnn.tw/lesson/article/excel/1872.html
分享到:
Copyright 1999 - 2020 Excel Home.All Rights Reserved.
本站特聘法律顧問:徐懷玉律師 李志群律師   滬ICP備11019229號

滬公網安備 31011702000001號

征信       
35选7摇奖器