Lookup函式的使用方法（vlookup函式用法示例）
LOOKUP函式非常強大，有引用函式之王的稱號。要想完全掌握它，必須瞭解它的5種常見用法和它的二分法查詢原理。由於相關知識點比較多，所以教程將分成上下兩篇。今天我們首先通過五個例子來了解這個函式的5種常見用法。明天我們再來說二分法查詢原理和之前文章中遺留的兩個LOOKUP問題。
一、常規引用
格式：LOOKUP（查詢值，查詢區域）
例1：根據姓名查詢語文成績，公式為=LOOKUP(H2,C2:D19)
例2：根據姓名查詢英語成績，公式為：=LOOKUP(H2,C2:F19)
通過這兩個例子我們可以發現，LOOKUP在進行查詢時公式的結構非常簡單，查詢值和要找的結果分別位於查詢區域的首列和末列。
但是僅僅瞭解這個用法是遠遠不夠的，如果我們再試一個資料的話，有可能就會發現問題：
當查詢姓名變成趙永福的時候，結果就不對了，這是因為LOOKUP函式使用的是二分法查詢，也就是模糊匹配，關於這一點，我們將在明天的教程《LOOKUP函式用法全解（下）——LOOKUP的二分法查詢原理》中詳細解釋。
因此，在使用LOOKUP進行常規查詢的時候，有一個非常重要的步驟，就是按照查詢內容（姓名所在的c列）升序排序。
當我們排序以後，公式的結果立刻變成了正確的，是不是很神奇！
這又引出了一個新的問題，如果資料不能排序的話，LOOKUP函式還能用嗎？
肯定能用啊，下面來看看LOOKUP函式的第二種用法。
二、精確查詢的套路
格式：=LOOKUP（1,0/(查詢範圍=查詢值),結果範圍）
在I2單元格輸入公式：
=LOOKUP（1,0/（C2:C19=H2），D2:D19）,回車，可看到正確結果。
關於這個套路的1和0/到底是什麼意思，也是提問率最高的問題之一，在未講解二分法原理之前，簡單來說一下公式的意思。1就是要查詢的值，但是條件變了，不是直接查詢姓名，而是根據姓名得到的一組邏輯值：
注意這裡只有一個TRUE，也就是我們要找的姓名。
接下來用0除以這些邏輯值，在進行計算的時候邏輯值TRUE代表1，FALSE代表0，當分母為0也就是FALSE的時候，計算結果是錯誤值：
因此，LOOKUP的工作就變成了在一組資料中找1。由於這組資料只有一個0，其他都是錯誤值，二分法使得LOOKUP只能找到不大於查詢值的最後一個數字，因此只能找到0，最後根據0的行位置（第2行）得到第三引數對應位置的資料，即D2就是我們需要的結果。
這部分內容算是函式學習中比較有難度的知識點了，初學者可能理解困難，這不要緊，隨著學習的深入，當對陣列和邏輯值這兩大要點掌握比較熟練的時候，這些內容就很好理解了。目前如果不能完全理解，記住這個套路就行：=LOOKUP（1,0/(查詢範圍=查詢值),結果範圍）。同時這個套路還能延伸出多條件精確查詢用法：
=LOOKUP（1,0/((查詢範圍1=查詢值1)* (查詢範圍2=查詢值2)* (查詢範圍3=查詢值3)),結果範圍）
就是在每個查詢範圍內找到要找的值，得到的邏輯值相乘後同時符合多個條件的位置就是1，原理與單條件的一樣。
三、反向查詢的套路
與我們熟知的VLOOKUP不同，使用LOOKUP函式進行反向查詢時非常簡單，公式結構為：
=LOOKUP（查詢值，查詢列，結果列），下面這個例子是按照姓名排序後再反向查詢的效果：
如果資料不能排序的話，使用精確查詢的套路：=LOOKUP(1,0/(C2:C19=H8),B2:B19)
四、按區間查詢的套路
根據學生的總分給出相應的評語。50分以下的為“很差”，50-100分的為“差”，100-150分的為“一般”，150-200分的為“較好”，200-250分的為“優秀”，250分及以上的為“能手”。
這裡用的公式為：
=LOOKUP(G2,{0,50,100,150,200,250;"很差","差","一般","較好","優秀","能手"})
按照評語的要求分成了六個等級，如果用if函式去做就很囉嗦，使用LOOKUP處理這類問題非常方便，公式結構也很簡單：
=LOOKUP（分數值，{下限1, 下限2……;評語1,評語2……}）
在寫這個公式的時候注意兩點：
1.LOOKUP的第二引數使用了常量陣列，這裡的大括號是手動輸入的，括號內用一個分號分開，左邊是每個等級的下限，例如50分以下這個表述裡下限就是0，50-100的下限就是50，以此類推，每個數字之間用逗號分開；分號右邊是對應的評語，評語應當使用引號，同時用逗號分開（公式裡的所有符號都是英文狀態下的）；
2.數字區間應當遵循升序的排列順序，否則結果就會錯誤。
五、關於資料排序的重要性
當我們按照學號查詢姓名的時候，發現會出現錯誤，學號也是按升序排列的啊，怎麼會錯？
這是一種最常見的錯誤，這裡的學號升序排列只是我們感覺如此而已，實際上升序的效果是這樣的：
在使用LOOKUP的時候，如果不使用精確查詢的套路，切記一定要排序才能保證公式結果的正確性。
小結
1、今天一共分享了五種LOOKUP的使用套路，分別是常規查詢、精確查詢、多條件查詢，反向查詢還有按區間查詢，初學者掌握這些套路學會去套用解決問題就可以了；
2、LOOKUP函式的查詢原理與我們之前學過的VLOOKUP不同，VLOOKUP函式的查詢方式叫做遍歷法，找到滿足條件的第一個值就會停止查詢，而LOOKUP函式使用的是二分法原理進行查詢，要找到滿足條件的最後一個值才會停止查詢，這一點在函式的說明文件裡也提到了；
我們可以通過一個簡單的測試來驗證這一點：
3、對資料來源按升序排列這一點很重要：
如果不能排序，那麼一定要使用這個套路：=LOOKUP（1,0/(查詢範圍=查詢值),結果範圍），這也是二分法的特性決定的；
4、LOOKUP這個函式很強大，同時也很難以理解，要想徹底弄清楚這個函式，必須瞭解二分法原理。明天的教程我們就來聊聊什麼是二分法原理，順便再把前一段時間遺留的兩個問題（LOOKUP解決四捨五入的問題和進行資料提取的問題）做個解釋。
Lookup函式二分法的原理
昨天咱們學習了LOOKUP函式的各種套路，也多次提到了LOOKUP函式的查詢是根據二分法來進行的，那麼到底什麼是二分法，今天就來聊聊這個問題。
還是用昨天的例子：按序號查詢成績，序號是升序排列的，公式=LOOKUP(J2,A2:D19)的結果正確。
一、二分法查詢原理
二分法查詢是把查詢範圍中的資料按照個數一分為二找到位於中間位置的一個資料，中間值，然後用我們的查詢值和中間值做比較。當中間值等於查詢值時，直接去獲取結果；當中間值小於查詢值時，則向下繼續進行二分法查詢比較（也就是在不含中間值在內的下方的那一半資料中繼續進行二分法查詢）；當中間值大於查詢值時，則向上繼續進行二分法查詢比較（也就是在不含中間值在內的上方的那一半資料中繼續進行二分法查詢）。如果如此二分到最後一個資料都未找到等於查詢值的資料：最後一個資料小於查詢值的，那就以最後一個資料的位置去獲取結果值；最後一個資料大於查詢值的，那就再向上找一個位置最靠近最後一個資料的小於或等於查詢值的資料，再以這個資料的位置去獲取結果。
只是這樣解釋估計很多朋友都會犯迷糊，我們結合上面的例子，具體看看如何通過序號5，找到語文成績79的。
第一次比較：查詢範圍A2~A19一共18個資料，中間位置是18÷2=9，即中間值是A10單元格的9。顯然查詢值5小於9，所以繼續向上在A2~A9中查詢；
提示：如果查詢範圍資料個數是單數，中間位置就是（個數 1）÷2，例如11行的話，中間位置就是（11 1）÷2=6；如果資料個數是偶數，中間位置就是（個數）÷2。
第二次比較：只有8個資料，中間位置是8÷2=4，中間值是A5單元格的4，查詢值5大於4，所以繼續向下在A6~A9中找。注意此時下面只有四個數，第一次查詢時直接將9下面的資料都排除了。
第三次比較：4個資料，中間值是A7的6，查詢值5小於6，所以向上找。此時只剩下一個資料A6單元格的5，與查詢值一致，因此就得到5所對應的D列資料79。
僅僅通過這樣一個例子要想明白二分法是很困難的，我們再看一個例子。將上表中的資料按照成績降序排列，還是按序號5查詢語文成績，公式不做修改。因為序號這一列的順序亂了，不是升序排列，結果就出現了錯誤，實際是79，公式得到的是94，這是怎麼回事呢？還是通過二分法來看看吧。
第一次查詢：中間值（第9個資料）是18，查詢值5小於18，因此向上在A2~A9中找；
第二次查詢：上面的8個資料，中間值（第4個資料）是8，查詢值5小於8，繼續向上在A2~A4中找；
第三次查詢：上面的3個資料，中間值是1，查詢值5大於1，向下找：
第四次查詢：現在只有A4單元格一個資料7，查詢值5小於7，因此以7為參考，向上找一個位置最靠近7，同時數值小於5或者等於5的資料，即A3單元格的1，由此獲得對應的語文值就是94。
通過這兩個例子，我想大家對於二分法應該有了一定的認識，關於這個原理，在函式說明裡只有一句話的介紹：
在實際應用中，我們可以不用去糾結二分法到底是怎麼回事，中間位置是什麼，該往下還是往上找，這都是函式的工作，我們只需要記住一點：資料一定要升序排列，如果不能升序排列，那麼就按照LOOKUP的精確查詢套路去設計公式。
二、LOOKUP實現資料的四捨五入
二分法原理就介紹這麼多，接下來需要解決之前遺留的兩個問題。
在5月12日的文章中，我們用LOOKUP解決了一個四捨五入的問題，結果大家紛紛留言要個解釋：
那麼引起大家熱議的這個公式到底是什麼呢？看下圖：
原來這個公式是用LOOKUP函式將一個數字百位以下全部捨去，實現了百分位取整。
在瞭解過二分法原理以後，是時候讓LOOKUP還大家一個解釋了。首先說明ROW（A:A）*100這部分。它其實就是得到了一組數字。為了讓大家看明白，把A:A範圍給小一點，我們用=ROW(A1:A31)*100作說明：
雖然單元格中只能看到一個100，實際上是31個數字，我們可以用f9功能鍵來看看具體內容：
ROW函式用於獲取單元格的行號，ROW(A1:A31)*100就是用A1到A31單元格的行號分別乘以100，得到一組百位取整的資料{100;200;300;……3000;3100}。
然後LOOKUP上場了。它要在上述得到的已經百位取整的陣列中查詢A1。因為陣列是按升序排列的，所以查詢A1的實質就是在陣列中找小於等於A1的最大值。
以2517.32為例，唯有2500是小於它的最大值，因此結果就是2500。有興趣的朋友可以自己用二分法原理去試試，看看對不對。
其他數字的查詢方式都是如此。這個公式之所以巧妙，就是把一個四捨五入的問題變成了查詢引用的問題，真是妙！
三、LOOKUP進行資料提取
在5月31日的文章中，我們又利用LOOKUP進行資料的提取，因此有了一個5000字的約會：
這次又是什麼問題呢？原題如圖所示提取學號：
咱們用上圖的第一個資料來解釋。在N1單元格中輸入“10張勇a”，然後在O1單元格中輸入公式=-LOOKUP(1,-LEFT(N1,ROW(1:9)))提取學號。
LOOKUP不是引用函式嗎，怎麼又可以提取數字了？
LEFT函式的第二個引數使用了一個陣列，ROW(1:9)相當於{1;2;3;4;5;6;7;8;9}。LEFT是把第一引數指定的資料從左邊開始提取，提取的長度由第二引數來確定。 LEFT按照陣列{1;2;3;4;5;6;7;8;9}提取，得到9個結果：
也就是從左邊開始提取1位，2位……一直到9位。因為LEFT提取的結果都是文字型別，在LEFT前面加上負號，就可以把其中的文字型數字轉為數值，文字變成錯誤值：
錯誤值被LOOKUP忽略，現在就變成了在{-1; -10}中找1。1比這組資料都大，根據二分法查詢原理，二分後只能向下找，直到最後一位小於1的資料。因此，我們可以簡單理解成：當查詢值大於查詢範圍中所有資料（不管是否是升序排列）時，LOOKUP的實質就是在找最後一個資料。
其實將公式中的1變成0也是可以的，因為0也比所有的負數大：
當前最後一個數是-10，所以我們在LOOKUP前面再加個負號，就變成10了，也就是我們需要提取的數字。
對於初學者來說，以上兩個案例中LOOKUP的用法過於高階，即便是通過這些介紹，估計也是一知半解，其實學習函式是有一個過程的，從不認識到了解，從瞭解再到掌握，這裡需要大量的練習和思考。只要大家保持積極樂觀的心態，能夠體會到學習函式的樂趣的時候，就離成功不遠了。
轉載：部落窩教育 如有侵權，聯絡作者，會刪除