VLOOKUP函數(shù)是我們在職場當(dāng)中非常好用的神器之一，它有多種的使用方法，也有很多的坑，我們花了3個小時，幫你梳理了VLOOKUP函數(shù)使用的所有方法，以及可能你使用過程可能會遇到的坑。
如果你想學(xué)會VLOOKUP函數(shù)，看這一篇文章就足夠了！這篇文章有點長，在這里我們就為各位準(zhǔn)備了這篇文章的結(jié)構(gòu)圖。
要想了解一個函數(shù)，我們就需要清楚知道，在什么樣的情況下，我們可以使用VLOOKUP函數(shù)，也就是VLOOKUP函數(shù)使用的條件是什么？2、表一當(dāng)中缺失的內(nèi)容，能夠在表二里面找到只要滿足上面的三個條件，我們就可以使用VLOOKUP函數(shù)，缺一不可。上面這個案例當(dāng)中，就有兩張表，左邊這張是員工信息表，右邊這張是工資表。另外，右邊這張工資表缺失的工資信息，能夠在左邊這張表當(dāng)中找到。
滿足了上面這三個條件，我們就可以使用VLOOKUP函數(shù)。VLOOKUP（查找的值，查找區(qū)域，返回列號，查找模式）
在上面這個案例當(dāng)中，我們通過函數(shù)公式：=VLOOKUP(F3,B3:D18,3,0)【B3:D18】：代表我們查找的數(shù)據(jù)區(qū)域范圍，注意這里不包含標(biāo)題。
這里要注意，這個列號是我們選定區(qū)域的列號，不是Excel表的列號。我們選定的區(qū)域是編號-部門-工資，一共是有3列，我們查找的值【工資】在該區(qū)域的第三列，所以返回的列號為3。
如果是按照表格的列號來看，工資所在的列是D列，D列是第四列，如果返回的列號錄入為4，那么公式的結(jié)果就會出錯。【0】：代表查找的模式，其中0代表精確查找，1代表模糊查找。精確查找：就是查找的數(shù)是一對一的關(guān)系，A查找的就是A，B查找的就是B，兩個數(shù)是完全相同的。
模糊查找：查找的是近似關(guān)系，A可以查找B，B可以查找C，不是一一對應(yīng)的關(guān)系的。以上就是VLOOKUP函數(shù)各個參數(shù)的定義以及使用的方法。
在下面這個列表當(dāng)中，我們要如果要根據(jù)員工編號，查詢員工的手機號碼，那么則可以在J3單元格錄入公式：
=VLOOKUP(H3,$B$3:$E$18,4,0)2、在B3到E18區(qū)域查找，就是在左側(cè)的表格內(nèi)查找，這里同樣不包括標(biāo)題。3、查找的結(jié)果在第4列，也就是我們要查找的是手機號碼。錄入完以上公式，我們再往下進(jìn)行錄入，即可把所有的員工的手機號碼查詢出來。2、根據(jù)學(xué)生編號，查詢學(xué)生檔案信息在下面的工作表中，我們要根據(jù)學(xué)生的編號，查詢得到后續(xù)的學(xué)生檔案信息，而且學(xué)生編號是亂序的，想一個個地去找不太可能。在下面的工作表中，我們要根據(jù)學(xué)生的編號，查詢得到后續(xù)的學(xué)生檔案信息，而且學(xué)生編號是亂序的，想一個個地去找不太可能。
這個時候我們就可以使用VLOOKUP函數(shù)來實現(xiàn)批量查詢。
我們在J24單元格錄入函數(shù)公式：=VLOOKUP($I24,$B$24:$G$31,2,0)這個函數(shù)公式表示：查找I24單元格的值，在B24到G31區(qū)域中查找，返回結(jié)果在第二列，以精確模式來查找。隨后我們往右邊拖動公式的時候，記得修改下結(jié)果返回的列號就可以了。這樣我們就完成了查找的值不在查找范圍內(nèi)的第一列信息的效果。錯誤一：查找的值不在查找范圍內(nèi)的第一列
如下圖所示，我們要查找雅客的工資是多少，通過VLOOKUP函數(shù)，我們得到了一個錯誤值。
其中的函數(shù)表達(dá)式為：=VLOOKUP(B7,E7:H8,2,0)
這里為什么會出錯了呢，因為我們查找的姓名，不是在查找范圍內(nèi)的第一列。
我們查找的區(qū)域E7到H8，第一列是序號，而我們查找的是姓名，所以它就產(chǎn)生了錯誤。
我們把查找的區(qū)域調(diào)整一下，將公式中原本的E7：H8改成G7:H8，那么就可以解決這個錯誤問題。
錯誤二：查找的值或者范圍沒有進(jìn)行鎖定
查找的值或者范圍，沒有進(jìn)行鎖定，也是導(dǎo)致VLOOKUP函數(shù)查找出錯的常見問題。
比如在下面這個表中，因為我們沒有對查找的值進(jìn)行鎖定。所以后續(xù)字段在查找的時候，就以前面一個單元格的值來進(jìn)行查找。
這個時候查找的值就已經(jīng)不是我們預(yù)想的值了，所以VLOOKUP函數(shù)查找就會出錯。
遇到這種需要橫列或者縱列拖動公式的情況，你應(yīng)該先檢查一下查找的值有沒有鎖定。就是通過光標(biāo)選中公式中的該單元格，然后按鍵盤上的F4鍵來進(jìn)行切換，在這里我們只要鎖定列就可以了，行不用鎖定，所以我們就讓美元符號出現(xiàn)在字母前面。鎖定了列之后，我們再往右邊拖拉公式，查找的值就不會再進(jìn)行移動了。
錯誤三：返回的列號不正確引起的錯誤
返回的列號不正確，也會引起VLOOKUP函數(shù)在查詢的時候出錯。
比如還是上面的例子，我們錄入完第一個公式的時候，往右邊去拖拉公式，結(jié)果你會發(fā)現(xiàn)，后面得到的所有結(jié)果，都是一樣的。
其實就是由于VLOOKUP函數(shù)的第三個參數(shù)，它不是引用的參數(shù)，而是一個固定的值，固定的值是不會隨著我們拖拉公式，產(chǎn)生改變的。
所以后面所有的結(jié)果，都是返回第二列，那么結(jié)果自然會出錯。在查詢的字段信息比較少的情況下，我們可以通過手動更改第三個參數(shù)的值，來讓它返回正確的列號。
但如果查找的字段過多，我們就可以借助其他函數(shù)，來實現(xiàn)引用的效果了，這個后續(xù)會再跟大家介紹。錯誤四：查找的值與區(qū)域的值格式不統(tǒng)一查找的值與區(qū)域的值格式不統(tǒng)一，這種錯誤，常常出現(xiàn)在一些學(xué)員的表格上。比如下圖，明明公式是沒有問題的，查找的值跟區(qū)域的值都一樣，為什么會產(chǎn)生錯誤呢？在該案例當(dāng)中，數(shù)據(jù)公式確實沒有錯。問題就出在，B5單元格的格式是文本格式，要查詢的區(qū)域E5：H6當(dāng)中的E6單元格是數(shù)字格式這里。一個是文本格式，一個是數(shù)字格式，系統(tǒng)就會判斷他們是兩個不同的值，那么結(jié)果就自然會出錯。
修正的方法就是：把B5單元格的格式改為數(shù)字格式，那么就可以讓結(jié)果顯示正確了。
如果單元格內(nèi)有空格和換行的情況，就跟上面的問題一樣，不容易察覺，但這也是導(dǎo)致VLOOKUP函數(shù)出錯的原因。如下圖所示，這兩個公式看起來也是沒啥問題，可結(jié)果卻是錯誤的。問題的根源就出在B6單元格的值，多了一個換行符，導(dǎo)致該單元格與E7單元格的格式不同，結(jié)果就出錯了。解決的方法就是：把B6單元格的換行符刪除掉，如果換行、空格的情況比較多，可通過查找替換的功能，查找換行符或空格，替換為那里不填寫，那么就可以實現(xiàn)批量地修改。如下圖所示，下面這個表格，跟上面幾個錯誤也是同樣，公式是正確的，但查詢的結(jié)果卻出錯了，那么多半是格式問題。細(xì)心的朋友，可以看到在B5單元格，數(shù)字2的左上角，又一個綠色的光標(biāo)，這綠色的光標(biāo)，就代表該單元格可能是文本格式。我們點擊該單元格，你會發(fā)現(xiàn)在公式欄中，在數(shù)字2的前面還有一個英文的單引號，而該單引號在默認(rèn)情況下，都是會隱藏的。這個就是導(dǎo)致最終查詢結(jié)果出錯的原因。解決的方式：利用查找替換功能，把單引號的字符都替換為空值，那么結(jié)果即可查詢正確。VLOOKUP函數(shù)的最后一個參數(shù)是查找模式，在不填寫的情況下，默認(rèn)是按照模糊查找的方式來查找的。所以下面這個案例，就出現(xiàn)了一個這樣的錯誤。
明明在數(shù)據(jù)源當(dāng)中沒有序號3，但是卻能查找到對應(yīng)的工資出來。究其根源，就是該同學(xué)在錄入公式的時候，忘記VLOOKUP函數(shù)還有第四個參數(shù)。而第四個參數(shù)，正是決定VLOOKUP函數(shù)使用精確查找還是模糊查找的重要條件。如果你不錄入第四個參數(shù)，系統(tǒng)就會默認(rèn)按照模糊查找的模式來查找，那么最終看似也能把結(jié)果算出來，但很可能你得到的都是錯誤的答案。所以一定要記住，VLOOKUP函數(shù)是有四個參數(shù)的。在VLOOKUP函數(shù)當(dāng)中，不支持反向的查找，因為它違反了我們上面提到的第一個錯誤。在這個公式當(dāng)中，我們要根據(jù)員工的姓名，查找出他所在的部門。而在數(shù)據(jù)源表中，部門是排在姓名的前面的。
我們以往通過VLOOKUP函數(shù)來查找，都是按照從左往右的方向來查找，在這里則是要反其道而行，從右往左進(jìn)行查詢，那么結(jié)果自然就出錯了。如果你硬是想要逆序查找，也不是不能，可以結(jié)合其他函數(shù)來實現(xiàn)，這個我們也是會在后面的內(nèi)容當(dāng)中，進(jìn)行介紹。
通過上面內(nèi)容的學(xué)習(xí)，你已經(jīng)初步掌握了VLOOKUP函數(shù)的使用方法了，接下來我們就來跟各位介紹一下VLOOKUP函數(shù)的進(jìn)階用法！我們都知道，VLOOKUP函數(shù)查找的模式，在大眾心目中都是一對一查找的，A查找的就是A，B查找的就是B。
但其實Vlookup函數(shù)可以實現(xiàn)這樣的精確查找之外，它還可以實現(xiàn)模糊查找的功能。
要查找的值和查找區(qū)域的值未必都是要一一對應(yīng)關(guān)系的。在上面這個案例當(dāng)中，我們要根據(jù)銷售人員的銷售額，自動匹配他們能夠得到的提成比率。
而每個檔位能夠拿到的提成比率區(qū)間，則是在左邊的這個表當(dāng)中。對于這樣的查找，我們?nèi)绻凑者^往的方式來統(tǒng)計，很有可能就會用到IF函數(shù)來進(jìn)行判斷。
但其實在這個案例當(dāng)中，我們用VLOOKUP函數(shù)會快10倍！
=VLOOKUP(G7,$C$7:$D$13,2,1)這個函數(shù)公式表示的就是模糊查找，它會自動根據(jù)你的最低值，給你匹配相應(yīng)的提成比率，這個就是區(qū)間查找的用法。與這個案例類似的，你可以學(xué)生的成績，自動給他們匹配考試的評級等。有時候，數(shù)據(jù)量太大，我們可能已經(jīng)不記得某些產(chǎn)品它的全稱是什么了？在不記得產(chǎn)品名稱全稱的時候，我們?nèi)绾芜M(jìn)行數(shù)據(jù)的查找和引用呢？這時候我們就可以用到Excel當(dāng)中的通配符【*】比如：一個人，這三個字，我們就可以用一個【*】來表示。在這個案例當(dāng)中，我們要通過我們記得的部分字段，比如【ABC】來查找出產(chǎn)品名稱中包含【ABC】的內(nèi)容，我們應(yīng)該如何設(shè)置呢？
=VLOOKUP('*'&F8&'*',$B$8:$C$14,2,0)這個公式表示，查找包含F(xiàn)8單元格字符串內(nèi)容的價格。其中：'*'&F8&'*' 表示的就是分別在F8單元格的內(nèi)容前后，加上一個任意字符串的通配符，來表示在F8內(nèi)容的前后還有其他信息。通過這樣的方式，我們就能夠?qū)崿F(xiàn)模糊查找。在查找的過程當(dāng)中，我們可能不僅有一個條件，可能有兩個或者兩個以上條件，要滿足這兩個條件，才去匹配我們想要值，那么VLOOKUP函數(shù)能不能實現(xiàn)多條件查找呢？正常情況下是不可以的，但我們可以VLOOKUP函數(shù)升級，給它添加一個武器。比如在上面這個案例當(dāng)中，我們要根據(jù)產(chǎn)品的日期和單號，來去查找該產(chǎn)品的入庫數(shù)量和入庫型號。多條件查找的解決方法跟單條件查找的模式基本一致，只是這中間我們要做一個轉(zhuǎn)換，我們需要把多條件變成單一條件。
比如，原本可能是A、B這兩個條件，我們通過連接符，連接AB，那么【AB】就變成的一個值。同樣，要查找的數(shù)據(jù)區(qū)域，也是可以通過連接符來進(jìn)行連接，這樣我們就實現(xiàn)了多條件與單條件的轉(zhuǎn)換。=VLOOKUP(G7&H7,IF({1,0},$B$7:$B$16&$C$7:$C$16,$D$7:$D$16),2,0)該公式有些長，而且還用到了數(shù)組函數(shù)的概念。=VLOOKUP(G7&H7,$B$7:$B$16&$C$7:$C$16,2,0)其中上面這段，表示的就是我們前面所講的，將多條件通過連接符合并，變成單條件，但這時候，你會發(fā)現(xiàn)，該公式缺少結(jié)果所在的行列。所以我們在里面就嵌套了一個IF{1，0}的數(shù)組。1和0在Excel當(dāng)中，也可以用來表示是與否的關(guān)系，也就是做出一個判斷。
如果是1，表示結(jié)果成立返回的值，如果是0,表示結(jié)果不成立返回的值。通過這個條件的判斷，我們把Vlookup函數(shù)的查找區(qū)域以及結(jié)果區(qū)域分別放置在成立返回的值以及不成立返回的值，那么就能夠把該公式補充完整。=VLOOKUP(G7&H7,$B$7:$B$16&$C$7:$C$16,2,0)。VLOOKUP函數(shù)與其他函數(shù)的結(jié)合單一個VLOOKUP函數(shù)其實已經(jīng)蠻強大了，但我們還可以結(jié)合其他函數(shù)，來給VLOOKUP函數(shù)賦能，實現(xiàn)他原本實現(xiàn)不了的功能。
MATCH函數(shù)的作用在于，查找某個字段在它所在字段當(dāng)中的序號。表達(dá)式為：MATCH（查找的值，查找的區(qū)域，查找模式）
比如在下面這個案例當(dāng)中，我們查找【班級】字段在所在字段列表當(dāng)中的第幾列，我們就可以使用函數(shù)：
得到的結(jié)果就是3，表示班級在所有字段當(dāng)中的第三列。這個功能有什么用呢，它就可以解決VLOOKUP函數(shù)第三個參數(shù)不能引用的問題。
在前面這個案例當(dāng)中，正是VLOOKUP函數(shù)第三個參數(shù)不能引用，所以才導(dǎo)致結(jié)果錯誤。我們可以借助MATCH函數(shù)，先計算出每個字段位于字段列表當(dāng)中的第幾個，再嵌套到VLOOKUP函數(shù)里面，這樣我們就不用手動更改第三個參數(shù)了。最終公式為：=VLOOKUP($I24,$B$24:$G$31,MATCH(J$23,$I$23:$N$23,0),0)其中：MATCH(J$23,$I$23:$N$23,0)部分，則是計算每個字段位于字段列表當(dāng)中的第幾個。這樣我們就通過函數(shù)讓第三個參數(shù)具備了引用的屬性。如果你覺得上面一個組合函數(shù)太難了，那么這個簡單的函數(shù)一定要學(xué)會。COLUMN函數(shù)的作用是計算某個單元格所在的列號。
它返回的結(jié)果是單元格所在的列號，跟行號沒有關(guān)系，A列就是1，B列就是2，C列就是3，以此類推。
這個函數(shù)同樣可以解決VLOOKUP函數(shù)第三個參數(shù)不能引用的問題。在之前的案例中，我們只需將第三個返回的列號，改成嵌套的COLUNM函數(shù)即可。
最終公式為：=VLOOKUP($I24,$B$24:$G$31,COLUMN(B2),0)其中COLUMN(B2）函數(shù)的作用就是返回該列所在的序號。下面是我們的財務(wù)報表，現(xiàn)在如果我們要根據(jù)上期余額，查找出對應(yīng)的科目，通過Vlookup函數(shù)來計算，系統(tǒng)就是提示錯誤。因為它違背了Vlookup函數(shù)的使用條件：查找的值，必須在查找范圍的第一列。但我們可以看到，查找的值，上期余額是在查找范圍內(nèi)的最后一列，所以如果我們硬要用VLookup函數(shù)去查找，就會提示錯誤。既然不能逆序查找，我們就可以想辦法，將這兩列的數(shù)值位置進(jìn)行調(diào)換。怎么轉(zhuǎn)換呢？前面我們就提到了一種方法，就是通過CHOOSE函數(shù)與VLOOKUP函數(shù)結(jié)合，利用Choose函數(shù)可以選擇返回的區(qū)域的效果來調(diào)換位置。
另外一種方法，就是我們今天要提到的，通過IF函數(shù)來實現(xiàn)轉(zhuǎn)換。
在企圖用IF函數(shù)來實現(xiàn)位置調(diào)換的時候，我們需要理解IF函數(shù)使用的原理，哪怕我們以為對IF已經(jīng)非常熟悉了。IF函數(shù)的表達(dá)式為：IF（判斷條件、條件成立返回值，條件不成立返回值）
比如下面這個案例：根據(jù)國家規(guī)定，2020年男性退休年齡為60歲，女性退休年齡為50歲。我們在公式欄當(dāng)中錄入函數(shù)公式：=IF(B15='男',60,50)這個公式表示：判斷B15單元格的值是否是男，如果是男，那么就顯示為True，如果是True，那么就返回條件成立返回的值60.
如果是FALSE，那么就返回條件不成立返回的值50.所以B16單元格的公式第一步運算后的結(jié)果就是：=IF(TRUE,60,50)
C16單元格第一步運算后的結(jié)果就是：=IF(FALSE,60,50)在Excel當(dāng)中，還有一個潛規(guī)則，Ture可以用1表示，F(xiàn)alse可以用0表示。但是，IF函數(shù)不僅可以返回1個單元格的值，也可以返回多個單元格的值。=IF({1,0},60,50)，結(jié)果返回60=IF({0,1},60,50)，結(jié)果返回50所以在這其中，我們不難發(fā)現(xiàn)，1和0的兩個數(shù)字的位置，能夠影響結(jié)果返回的值。利用這個規(guī)律，我們就可以解決Vlookup函數(shù)不支持逆序查找的問題了。
我們在G3單元格錄入函數(shù)公式：=VLOOKUP(F3,IF({1,0},D3:D9,C3:C9),2,0)錄入完公式后，記得按Ctrl SHIFT ENTER三個按鍵進(jìn)行確認(rèn)，因為這是數(shù)組函數(shù)。=VLOOKUP(F3,{999,'現(xiàn)金';123,'銀行存款';445,'建設(shè)銀行';245,'保證金賬戶';633,'應(yīng)收賬款';234,'其他應(yīng)收款';562,'固定資產(chǎn)'},2,0)其中中間的'現(xiàn)金';123,'銀行存款';445,'建設(shè)銀行';245,'保證金賬戶';633,'應(yīng)收賬款';234,'其他應(yīng)收款';562,'固定資產(chǎn)“你可以把它理解為，就是兩列數(shù)據(jù)，一列是科目，一列是上期余額，科目排在前面，上期余額排在后面。而函數(shù)公式當(dāng)中的：IF({1,0},D3:D9,C3:C9)這一部分則表示，將D3到D9和C3到C9兩列的位置進(jìn)行調(diào)換。D3和D9原本是排在后面的，現(xiàn)在顯示到前面，C3和C9原本是在表格前面的，現(xiàn)在顯示到后面。通過這兩列數(shù)據(jù)進(jìn)行位置的調(diào)換，那么剩下的就可以按照Vlookup函數(shù)的方式，進(jìn)行數(shù)據(jù)的查找了。看了這么多VLOOKUP函數(shù)的用法，我們來總結(jié)一下使用VLOOKUP函數(shù)的優(yōu)勢。優(yōu)勢1：可以批量查詢合并數(shù)據(jù)VLOOKUP函數(shù)最吸引職場人的一點就是可以可以批量地查詢以及合并數(shù)據(jù)，無論你是幾百條還是幾千條數(shù)據(jù)，無論你的字段排序有多亂，它都可以批量幫你找出來。優(yōu)勢2：數(shù)據(jù)更新會產(chǎn)生聯(lián)動效應(yīng)VLOOKUP函數(shù)的的第二大優(yōu)勢就是數(shù)據(jù)數(shù)據(jù)更新的時候會產(chǎn)生聯(lián)動效應(yīng)，用牽一發(fā)而動全身來形容它也不為過。所以VLOOKUP函數(shù)也多用在財務(wù)系統(tǒng)的構(gòu)建，通過VLOOKUP函數(shù)把多張報表串聯(lián)起來，形成一張超級大的信息網(wǎng)絡(luò)。通過前面的案例大家也可以看到，VLOOKUP函數(shù)查詢的方向一般來說只能自左往右，自上而下。如果想要實現(xiàn)逆序查找，那么則要借助其他函數(shù)。如果想要進(jìn)行多條件查詢，VLOOKUP函數(shù)本身也是不支持的，同樣也是需要借助外力，這對于小白來說，比較困難。返回的列號不能引用，也是VLOOKUP函數(shù)的一大弊端，常常需要手動更改或者借助其他函數(shù)來實現(xiàn)， 不能一步到位。雖然VLOOKUP函數(shù)在此之前一直受到職場人的寵愛，但VLOOKUP函數(shù)的未來可能會逐漸淡出我們的視線。因為Excel當(dāng)中有更加強大的PV和PQ功能，不用錄入公式，就能實現(xiàn)VLOOKUP函數(shù)的功能。在未來，還有XLOOKUP函數(shù)，作為VLOOKUP函數(shù)的增強版，解決了VLOOKUP函數(shù)過往所解決不了的問題，使用起來更加方便。但對于初級的小白來說，VLOOKUP函數(shù)還是我們學(xué)習(xí)Excel必備的函數(shù)之一，它能夠幫助我們提升工作效率，實現(xiàn)多表聯(lián)動，也是我們辦公的好幫手！回復(fù)關(guān)鍵詞【練習(xí)142】領(lǐng)取
本站僅提供存儲服務(wù)，所有內(nèi)容均由用戶發(fā)布，如發(fā)現(xiàn)有害或侵權(quán)內(nèi)容，請
點擊舉報。
