編按：

從VLOOKUP的四個(gè)參數(shù)來(lái)深度解讀VLOOKUP的使用規(guī)則和用法。包括最新用法：不用下拉填充公式即可完成所有查找值的查找。

一說(shuō)起查找函數(shù)，大部分人都會(huì)想到VLOOKUP函數(shù)。

作為一個(gè)有34年歷史的老牌函數(shù)，VLOOKUP也算是聲名遠(yuǎn)揚(yáng)，無(wú)愧 “查找之王”的美稱。但在我看來(lái)，他既有光鮮的一面，也有一堆糗事，與他的笨拙和固執(zhí)分不開(kāi)。

怎么說(shuō)？

今天就從VLOOKUP函數(shù)的4個(gè)參數(shù)來(lái)深度解讀VLOOKUP，同時(shí)介紹最新用法：在第一和第三參數(shù)引用數(shù)據(jù)區(qū)域或者數(shù)組，不用下拉填充公式即可完成所有查找值的查找。

分別是：查找值，查找區(qū)域，返回列，匹配類型。

第一參數(shù)查找值：表里如一的堅(jiān)守者；靈活變通和與時(shí)俱進(jìn)者！

在這里我們可以看到VLOOKUP成為之所以成為明星函數(shù)的原因：堅(jiān)守、靈活、與時(shí)俱進(jìn)。

1.表里如一

表里必須如一，來(lái)不得半點(diǎn)虛假。這是VLOOKUP的笨拙之處也是最可貴的地方！

如下圖，我們要查找編號(hào)為714848的銷售金額，Excel卻給我們返回了一個(gè)錯(cuò)誤值，這是咋回事呢？

取消A列和G列居中對(duì)齊，G2單元格的數(shù)字靠左，而A9單元格的數(shù)字靠右，原來(lái)G2和A9屬性不一樣！

Vlookup函數(shù)有自己的規(guī)則，查找的時(shí)候嚴(yán)格遵守【表里如一】的第一定律：數(shù)據(jù)屬性一致。

如果查找值是文本，那么查找區(qū)域里對(duì)應(yīng)的值也應(yīng)該是文本；

如果查找值是數(shù)字，那么查找區(qū)域里對(duì)應(yīng)的值也應(yīng)該是數(shù)字。

再往下查，懊惱繼續(xù)，怎么又被Vlookup發(fā)了一個(gè)好人“NA”卡？

通過(guò)取消對(duì)齊，排除了數(shù)據(jù)屬性不一致的原因。

考慮是數(shù)據(jù)違反了第一參數(shù)【表里如一】的第二定律：字符數(shù)相等！

查找值與查找區(qū)域中的比對(duì)值必須字符數(shù)相等。

表面看到的字符是一樣的，但因?yàn)橛锌崭窕蛘卟豢梢?jiàn)字符的存在，兩者實(shí)際字符數(shù)可能不等，是不一樣的。

用LEN函數(shù)檢查字符數(shù)：

A2單元格的字符數(shù)=LEN(A2)=8，G2單元格的字符數(shù)=LEN(G2)=7。字符數(shù)不相等，兩者肯定不相等啦，也就查不到了。

TIPS：

幾個(gè)常見(jiàn)的Excel不可見(jiàn)字符，水平制表符char(9)、換行符char(10)、空格符char(32)。

在單元格輸入公式=char(9)即可得到一個(gè)不可見(jiàn)的水平制表符，其雖然不可見(jiàn)，但字符數(shù)為1。

彩蛋：如何糾正Vlookup看著有卻查找錯(cuò)誤的問(wèn)題？

對(duì)于數(shù)據(jù)屬性不一樣的：網(wǎng)上有很多方式可以在文本數(shù)字和數(shù)字之間轉(zhuǎn)換，這里就不說(shuō)了。

不可見(jiàn)字符造成字符數(shù)不一樣的：用公式=CLEAN(TRIM(SUBSTITUTE(要清洗的某單元格,CHAR(32),)))可以清除空格和常見(jiàn)的不可見(jiàn)字符。清洗后再?gòu)?fù)制并選擇性粘貼覆蓋原來(lái)數(shù)據(jù)即可。

2.靈活變通——支持通配符查找和支持多條件連接查找

表里如一的堅(jiān)守并不等于死板和不知變通！

相反，第一參數(shù)非常靈活。

如果查找值本身不完整，如是簡(jiǎn)稱，我們可以通過(guò)添加通配符“*”或者“?”來(lái)進(jìn)行包含查找，如下圖，查找鞋，即為查找包含鞋字的：

VLOOKUP的第一參數(shù)還支持用&連接多個(gè)單元格的內(nèi)容，用于多條件查詢：

3.與時(shí)俱進(jìn)——引用數(shù)據(jù)區(qū)域或數(shù)組進(jìn)行查找

這是Vlookup最新用法！
在低版本的Excel中，Vlookup的第一參數(shù)通常是一個(gè)數(shù)據(jù)，但在最新的office365、office2021版本中，VLOOKUP函數(shù)的第一參數(shù)可以直接引用數(shù)據(jù)區(qū)域或數(shù)組。有了這項(xiàng)支持，不需要下拉填充公式即可完成所有查找值的查詢。如下，直接在I2中輸入公式，第一參數(shù)引用數(shù)據(jù)區(qū)域G2:G7，即可完成所有訂單查找。

第二參數(shù)查找區(qū)域：圈地愛(ài)好者與固執(zhí)的向右查找者！

1.圈地愛(ài)好者

要查找先圈地。把包含查找值和返回值所在的整片區(qū)域都圈起來(lái)作為自己的領(lǐng)地，然后只在領(lǐng)地里進(jìn)行查找，其他地方恕不接待。

比如下圖中的B到E列就是此次查找的領(lǐng)地（查找區(qū)域）。

這點(diǎn)與它的兄長(zhǎng)Lookup迥然不同，Lookup可以分別指定查找區(qū)域和返回區(qū)域，而不需要把兩者圈在一期。

2. 固執(zhí)的向右查找者！

這塊地從哪里開(kāi)始圈起？往哪個(gè)方向圈？往哪個(gè)方向查？

VLOOKUP要求領(lǐng)地中左起的第一列必須是查找值所在列，然后向右圈地；圈地后，查找也是從領(lǐng)地左起的第一列開(kāi)始往右查。

譬如：查找值為訂單編號(hào)，那么我們就要以訂單編號(hào)這一列為第一列，向右進(jìn)行圈地，直到圈到要查詢的結(jié)果值——銷售金額這一列為止。然后在B列中查找訂單“762145”，找到后水平往右走，直到返回列。

一個(gè)小問(wèn)題，能向左進(jìn)行圈地嗎？

比如下圖，我們需要根據(jù)訂單編號(hào)來(lái)查找銷售部門，我們可以選中B列再向左邊A列開(kāi)始圈嗎？

答案是可以！但選擇的區(qū)域如B1:A15在公式輸入完畢后自動(dòng)會(huì)更正為A1:B15。

那能否向左查找呢？

答案是不可以！

TIPS

如果必須從右往左查找，可以請(qǐng)來(lái)IF這個(gè)熱心腸的函數(shù)將這兩列內(nèi)容顛倒一下順序，即用IF({1,0}把B列和A列組合在一起，并把 B列放在A列前面。

TIPS：

如果有向右或者向下復(fù)制公式的需求，我們還需要給這塊查找區(qū)域上個(gè)保險(xiǎn)。

通俗一點(diǎn)就是說(shuō)，不想你千辛萬(wàn)苦圈起來(lái)的地到處亂跑，最好給它修個(gè)柵欄。修柵欄，就得花錢對(duì)不對(duì)？

所以我們用這個(gè)$符號(hào)，將它放在你需要固定的數(shù)據(jù)前面，這樣，拖拽公式的時(shí)候，查找區(qū)域就不會(huì)再變化了。

TIPS：

VLOOKUP的查找區(qū)域是只能圈一塊地，還是可以圈多塊地呢？

比如現(xiàn)在我們要找西紅柿的產(chǎn)量、銷量、利潤(rùn)，而這些數(shù)據(jù)分別放在三個(gè)Excel表里，又該如何寫公式？

借助INDIRECT函數(shù)可以實(shí)現(xiàn)多表查詢數(shù)據(jù)，如下圖所示：

第三參數(shù)返回列：笨拙的數(shù)數(shù)者

1.默認(rèn)數(shù)數(shù)

返回第幾列呢？VLOOKUP是邊走邊數(shù)數(shù)來(lái)確定返回列數(shù)的。從領(lǐng)地的第一列開(kāi)始，一步一列，走到返回列有幾步，就寫幾列。

尤其是同樣的條件需要查找多列返回值的時(shí)候，每次都去數(shù)一次然后手動(dòng)修改，特麻煩。

2.找朋友相助自動(dòng)給出返回?cái)?shù)

在查找多列返回值時(shí)，為避免數(shù)來(lái)數(shù)去以及改來(lái)改去出錯(cuò)，那就只有求人相助了。

VLOOKUP：各位大哥大姐，小弟能力有限，實(shí)在是活不下去了。俗話說(shuō)在家靠父母，出門靠朋友……

眾函數(shù)覺(jué)得VLOOKUP不擺明星架子，還算真誠(chéng)，于是紛紛效犬馬之勞。

（1）COLUMN，搞定有序變化的返回列

如圖所示，要找出A、B、D產(chǎn)品在1、2、3月的銷量，如果單單是靠VLOOKUP的話，只能頻繁地去修改第三參數(shù)，于是COLUMN雪中來(lái)送碳。

=VLOOKUP($A14,$A$1:$G$10,COLUMN(B2),FALSE)

（2）MATCH——自動(dòng)識(shí)別返回列

如果不是1月、2月、3月這樣的有序排列，而是1月、3月、5月的序列，還有MATCH函數(shù)絕渡逢舟。

=VLOOKUP($A14,$A$2:$G$10,MATCH(B$13,$A$1:$G$1,0),FALSE)

3.支持?jǐn)?shù)組一次返回多個(gè)值

這也是VLOOKUP的最新用法！
在低版本中，雖然Vlookup的第三參數(shù)可以輸入數(shù)組，但需要提前選中多個(gè)單元格后再來(lái)輸入公式并三鍵回車?，F(xiàn)在不一樣，直接選中第一個(gè)單元格輸入數(shù)組，直接回車，結(jié)果會(huì)返回一組數(shù)。如下圖求不同產(chǎn)品一二季度的銷量。

將Vlookup的第三參數(shù)用花括號(hào)括起來(lái)，2、 3 、 4，這三個(gè)數(shù)據(jù)分別對(duì)應(yīng)著 “產(chǎn)品A” 在1 、2、3月份的銷售數(shù)量。然后在外面套一個(gè)SUM函數(shù)就得到了A產(chǎn)品第一季度的銷量。

數(shù)組公式，低版本（office365、2019、2021等以下）的，需按Ctrl+Shift+Enter三鍵結(jié)束。

第四參數(shù)匹配類型：失誤的反人類設(shè)計(jì)

1.反人類設(shè)計(jì)

第4參數(shù)是個(gè)可選參數(shù)，用于設(shè)置匹配類型。匹配類型有近似匹配（TRUE或1）和精確匹配（FALSE或0）兩種。

默認(rèn)為近似匹配，可以省略不寫，而精確匹配則須給出參數(shù)，這和我們?nèi)粘Ｖ饕樵冃枨蟆非缶_——截然不同。

如下圖所示，我們要查找編號(hào)為“786029”的銷售數(shù)量，保持默認(rèn)不填寫第4參數(shù)，那對(duì)不起，只能得到一個(gè)錯(cuò)誤的答案。

這就意味著，每次輸完前三個(gè)參數(shù)時(shí)，你都需要謹(jǐn)慎地寫出第4參數(shù)（FALSE或0），或者你至少要在第三參數(shù)后加一個(gè)逗號(hào)，才能精確查找。

2.也并非一無(wú)是處——做區(qū)間查找很合適

默認(rèn)近似匹配也并非一無(wú)是處，當(dāng)我們做區(qū)間查找，如根據(jù)數(shù)據(jù)查等級(jí)時(shí)，就可以少寫一個(gè)參數(shù)，很省事。

譬如查A列的銷售等級(jí)，只用三個(gè)參數(shù)即可。

注：利用近似匹配做區(qū)間查找時(shí)，查找區(qū)域首列必須是升序排列。Vlookup近似匹配時(shí)，其查找方法與Lookup函數(shù)一樣，都采用二分法進(jìn)行。需要了解的可以看《一文講透LOOKUP二分法原理》。

VLOOKUP作為查找明星，很多Excel人都認(rèn)他、用他。我們從四個(gè)參數(shù)入手，去掉光環(huán)，看到他的本真，有堅(jiān)守，有笨拙，有固執(zhí)，有失誤。同時(shí)，Excel高版本中Vlookup有最新用法，可以直接用數(shù)組做參數(shù)，不用下拉填充公式即可完成所有查找。

有了這些理解，相信大家再使用VLOOKUP可以提前規(guī)避很多錯(cuò)誤并提高效率。

本文配套的練習(xí)課件請(qǐng)加入QQ群：902294808下載。

做Excel高手，快速提升工作效率，部落窩教育《一周Excel直通車》視頻和《Excel極速貫通班》直播課全心為你！

掃下方二維碼關(guān)注公眾號(hào)，可隨時(shí)隨地學(xué)習(xí)Excel：

相關(guān)推薦：

如何提取品牌信息？LOOKUP函數(shù)有絕招！

如何在交叉查詢中使用VLOOKUP？看完就懂！

Excel教程：如何制作帶有層次和透視感的圖表？

八大查找函數(shù)公式，輕松搞定數(shù)據(jù)中的多條件查找

版權(quán)申明：

本文作者逍遙；同時(shí)部落窩教育享有專有使用權(quán)。若需轉(zhuǎn)載請(qǐng)聯(lián)系部落窩教育。