【原】VLOOKUP函數(shù)的七種經(jīng)典用法，你會幾種？

初風(fēng)Excel教學(xué) 2022-02-22

展開全文

VLOOKUP函數(shù)是Excel中廣泛使用的查找類函數(shù)，其語法為VLOOKUP (查找值,查找范圍,查找列數(shù),精確匹配或近似匹配) 。本文為大家分享Vlookup函數(shù)的七種常用用法。

一、常規(guī)查詢

如下圖所示，根據(jù)G2單元格的姓名在C2：E7數(shù)據(jù)區(qū)域查找對應(yīng)的工齡，H2單元格的公式為=VLOOKUP(G2,$C$2:$E$7,3,FALSE)

常規(guī)查詢中，查找列“姓名”必須在返回值列“工齡”的左邊，從左向右查詢，即正向查詢。

二、反向查詢

如下圖所示，根據(jù)姓名查詢工號，從右向左查詢，是反向查詢。H2單元格的公式為=VLOOKUP(G2,IF({1,0},$C$2:$C$7,$B$2:B7),2,FALSE)

Vlookup函數(shù)不能從右向左查詢，因此必須使用if函數(shù)構(gòu)造一個姓名在左工號在右的數(shù)組。IF函數(shù)中的1代表True，返回第二個參數(shù)（即C2：C7）；0代表False，返回第三個參數(shù)（即B2：B7）。IF({1,0},$C$2:$C$7,$B$2:B7)返回的數(shù)組為{"楊曉明","001";"林桂香","002";"張華","003";"劉國明","004";"李敏","005";"許欣","006"}。

三、多條件查詢

如下圖所示，B2：E7數(shù)據(jù)區(qū)域中有兩個“張華”，男“張華”和女“張華”，現(xiàn)在需要查詢女“張華”的工齡。這是根據(jù)多個條件進(jìn)行查詢。在I2單元格輸入公式

=VLOOKUP(G2&H2,IF({1,0},$C$2:C7&$D$2:$D$7,$E$2:$E$7),2,FALSE)，按Ctrl+Shift+Enter。

本例使用IF函數(shù)將“姓名”列和“性別”列合并為一列，IF({1,0},$C$2:C7&$D$2:$D$7,$E$2:$E$7)生成的內(nèi)存數(shù)組為{"張華男","3年";"林桂香女","2年";"張華女","1年";"劉國明男","5年";"李敏女","3年";"許欣女","1年"}。

四、一對多查詢

如下圖所示，B1:C8為省份城市對照表，一個省份對應(yīng)多個城市?，F(xiàn)在要求根據(jù)E2單元格的省份返回對應(yīng)的所有城市。這屬于一對多查詢，根據(jù)一個查找值返回多個值。

在F2單元格輸入公式

=IFERROR(VLOOKUP($E$2&ROW(A1),IF({1,0},$B$2:$B$8&COUNTIF(INDIRECT("B2:B"&ROW($2:$8)),$E$2),$C$2:$C$8),2,FALSE),"")，按Ctrl+Shift+Enter鍵結(jié)束公式輸入。

公式解析：

1、$E$2&ROW(A1)是為了對省份進(jìn)行編號。在F2單元格$E$2&ROW(A1)返回“浙江1”。由于ROW(A1)是相對引用，向下復(fù)制公式時，會依次生成“浙江2”、“浙江3”等。

2、INDIRECT("B2:B"&ROW($2:$8)返回一組區(qū)域，返回的結(jié)果為

{“B2:B2”;”B2:B3”;”B2:B4”;”B2:B5”;”B2:B6”;”B2:B7”;”B2:B8”}

3、COUNTIF(INDIRECT("B2:B"&ROW($2:$8)),$E$2)計算E2單元格的省份在不斷擴(kuò)展的區(qū)域中出現(xiàn)的次數(shù)，結(jié)合B2：B8對B列的省份進(jìn)行編號。

4、使用IF函數(shù)返回對省份進(jìn)行編號的新的內(nèi)存數(shù)組。

IF({1,0},$B$2:$B$8&COUNTIF(INDIRECT("B2:B"&ROW($2:$8)),$E$2),$C$2:$C$8)返回的結(jié)果為{"廣東0","廣州";"浙江1","杭州";"江蘇1","南京";"廣東1","深圳";"浙江2","溫州";"江蘇2","蘇州";"浙江3","寧波"}。

五、使用通配符查詢

如下圖所示，查找姓名中有“桂”字的員工的工齡?！?”代表任意字符。

六、近似查詢

如下圖所示，根據(jù)C列銷售額在F2：G6區(qū)域查找對應(yīng)的提成比率。在D2單元格輸入公式=VLOOKUP(C2,$F$2:$G$6,2,TRUE)。參數(shù)“True”代表近似匹配，Vlookup會返回小于查找值的最大值對應(yīng)的提成比率。查找區(qū)域的F列必須按升序排序。