Question to say "I can!"

Excel最全逆向查詢(VLOOKUP,INDEX,LOOKUP)

2016-12-16

在日常的Excel函數應用中,最常用的就是查詢類函數,比如說根據工號查詢姓名、根據學生查詢成績、根據男豬腳查詢女一號等等。

說到查詢類函數,大家對VLOOKUP一定不會陌生,這可是人見人愛花見花開的函數。

如下圖,查詢明星的配偶:

E2單元格公式為
=VLOOKUP(D2,A:B,2,0)
意思就是以D2單元格的姓名作為查詢值,以A:B為查找區域,在首列中找到與D2單元格相同的姓名,然后返回這個區域中與之對應的第二列(也就是配偶所在列)的配偶。

如果我們以配偶作為查找值,需要在這個區域中查找和配偶對應的姓名,該如何使用公式呢?

因為VLOOKUP函數要求查詢值必須處于查詢區域的首列,再使用普通方法就無法完成要求了,今天就和大家說說,關于逆向查詢的幾種方法。

方法一:使用IF函數重新構建數組。

在E4中輸入
=VLOOKUP(D4,IF({1,0},$B$2:$B$17,$A$2:$A$17),2,0)

這個公式的用法就是用IF({1,0},$B$2:$B$17,$A$2:$A$17),返回一個配偶在前,姓名在后的多行兩列的內存數組,使其符合VLOOKUP函數的查詢值處于查詢區域首列的條件,再用VLOOKUP查詢即可。

該函數使用比較復雜,運算效率低。

裝逼指數:★★★★★
推薦指數:★

方法二:使用CHOOSE函數重新構建數組。

E6使用公式為:
=VLOOKUP(D6,CHOOSE({1,2},B2:B17,A2:A17),2,0)

這個公式的原理也是重新構建一個內存數組,使其符合VLOOKUP函數的查詢值處于查詢區域首列的條件。

該函數與方法一的思路相同,同樣是使用復雜,運算效率低。

裝逼指數:★★★★★
推薦指數:★

方法三:INDEX+MATCH結合使用。

E8使用公式為:
=INDEX(A:A,MATCH(D8,B:B,))

公式首先使用MATCH函數返回D8單元格姓名在B列單元格中的相對位置,也就是這個區域中所處第幾行。

再以此作為INDEX函數的索引值,從A列單元格區域中返回對應位置的內容。

這個公式是最常用的查詢公式之一,看似繁瑣,實際查詢應用時,由于其組合靈活,可以完成從左至右、從右到左、從下到上、從上到下等多個方向的查詢。

該函數是嵌套使用,操作靈活,運算方便。

裝逼指數:★★★
推薦指數:★★★

方法四:所向披靡的LOOKUP函數。

E10使用公式為:
=LOOKUP(1,0/(D10=B2:B17),A2:A17)

這是非常經典的LOOKUP用法。

首先用D10=B2:B17得到一組邏輯值,再用0除以這些邏輯值,得到由0和錯誤值組成的內存數組。再用1作為查詢值,在內存數組中進行查詢。

如果 LOOKUP 函數找不到查詢值,則它與查詢區域中小于或等于查詢值的最大值匹配,因此是以最后一個0進行匹配,并返回A2:A17中相同位置的值。

該函數使用簡便,功能強大,公式書寫也比較簡潔。

裝逼指數:★
推薦指數:★★★★★

如果有多條符合條件的結果,前三個公式都是返回首個滿足條件的值,而第四個公式則是返回最后一個滿足條件的值,這一點大家在使用時還需要特別注意。

關于多條件查詢,可以《一對多查詢,神器index+small》。

作者:admin | Categories:辦公能手 | Tags:

發表評論

電子郵件地址不會被公開。 必填項已用*標注

*

澳洲f1赛车b盘开奖套路 山西扣点点最新版 福彩3d和值尾振幅走势图彩宝贝 里美 尤利娅 中文字幕在线 3d今天开奖号码结 app南京麻将来了免费下载 11选5上海 探陵人 体彩31选7 基金配资平台 山西天星棋牌安卓 一分钟赛车稳赚规律 35选7 天津快乐10分走势 福彩 新疆35选7的规则 乌鲁木齐一条龙微信交流群 山西11选5的和值表