編按：

LOOKUP查找為何需要升序排列？VLOOKUP模糊查找為何也需要升序排列？如果不升序排列會(huì)有什么后果？今天就給大家詳細(xì)介紹。這個(gè)問題的解答涉及到Excel二分法查找本質(zhì)。了解本文后，大家就會(huì)掌握VLOOKUP或LOOKUP區(qū)間查找的做法，就會(huì)掌握用坐字法查最后一條文本的做法，就會(huì)掌握LOOKUP不排序的精確查找做法。

LOOKUP,作為一個(gè)老牌函數(shù)，越是難的查找問題，用起來越是得心應(yīng)手，（參考部落窩官網(wǎng)《VLOOKUP&LOOKUP雙雄戰(zhàn)》系列）。LOOKUP這么牛，但長久以來很少有人能真正注意到它，甚至在官方的“個(gè)人介紹”里，也常被當(dāng)作其他兄弟VLOOKUP、XLOOKUP的襯托。

究其原因，還是在于LOOKUP復(fù)雜的查找邏輯——二分法，嚇退了不少新手。

今天就從LOOKUP查找為何需要升序排列講起，用簡單、生動(dòng)的例子讓大家捋明白、學(xué)透徹，從此多一個(gè)查找利器。

一、LOOKUP查找為何需要升序排列？
LOOKUP查找需要數(shù)據(jù)升序排列，這是為什么呢？要知道答案，首先就得了解Excel的查找邏輯。Excel中包括兩種查找邏輯：遍歷法查找和二分法查找。

1. 遍歷法查找

遍歷法查找就是在指定的數(shù)據(jù)區(qū)域內(nèi)從上到下逐個(gè)把數(shù)據(jù)拿出來與查找值進(jìn)行比較的一種查找方法。

VLOOKUP精確查找用的就是遍歷法查找。

如下圖所示：查找薪資10000的級(jí)別。

公式：=VLOOKUP(D2,A2:B14,2,0)

VLOOKUP在A2:A14中進(jìn)行查找，取第一個(gè)數(shù)字，2000，不對(duì)；3000，也不對(duì)；4000，不對(duì)……直到第9次取到10000，終于與查找值對(duì)上了，然后返回級(jí)別I。

遍歷法查找，不考慮排序，只機(jī)械地從上到下逐一比較即可，很好理解也方便使用。

但是慢！

如果有10000個(gè)數(shù)據(jù)，要查找的值恰好位于第10000位，那就要查找一萬次！

有沒有快一點(diǎn)的方法？

有，二分法！

2.二分法查找

二分法查找就是逐次把數(shù)據(jù)分成上下兩半（二分），用每個(gè)中間值與查找值進(jìn)行比較查找的方法。中間值就是二分時(shí)位于中間位置的數(shù)值。每次二分，就把數(shù)據(jù)區(qū)域縮小一半，自然提高了速度。

怎么確定中間位置？

如果查找范圍數(shù)據(jù)個(gè)數(shù)是單數(shù)，中間位置就是（個(gè)數(shù)+1）÷2；如果數(shù)據(jù)個(gè)數(shù)是偶數(shù)，中間位置就是（個(gè)數(shù)）÷2。

LOOKUP查找就是典型的二分法查找。

公式=LOOKUP(D3,A2:A14,B2:B14)

第一次二分：

中間值是8000，小于查找值10000，說明我們要查找的數(shù)據(jù)在中間值下方的區(qū)域。

第二次二分：

中間值是11000，大于查找值10000，說明我們要查找的值是在上方區(qū)域。

第三次二分：

中間值是9000，小于查找值10000，說明要向下查。

觸底取值：

只有一個(gè)值了10000，此時(shí)“觸底反彈”向上查， 向上區(qū)域中第一個(gè)數(shù)10000與查找值相等作為查找結(jié)果值，最后我們返回結(jié)果值對(duì)應(yīng)的級(jí)別I。

只經(jīng)歷4次查找，我們就找到了正確值。

相比VLOOKUP用遍歷法經(jīng)歷9次查找，孰優(yōu)孰劣一目了然對(duì)不對(duì)？

用法總結(jié)：

當(dāng)中間值小于查找值時(shí)，則向下查（不含中間值在內(nèi)的下方的數(shù)據(jù)中繼續(xù)二分查找）；

當(dāng)中間值大于查找值時(shí)，則向上查（不含中間值在內(nèi)的上方的數(shù)據(jù)中繼續(xù)二分查找）；

到最后一個(gè)值，觸底向上查。

3.LOOKUP查找升序排列的原因

弄清楚了遍歷法查找和二分法查找的工作原理后，我們就可以回答LOOKUP查找為何需要數(shù)據(jù)升序排列了。

第一，這是二分法分區(qū)查找方法的必然。
二分法把數(shù)據(jù)分成上下兩個(gè)區(qū)域。當(dāng)中間值小于查找值，就往下查，當(dāng)中間值大于查找值往上查。反復(fù)這個(gè)過程，快速縮小范圍，快速逼近查找值。這個(gè)過程很像猜價(jià)格游戲。如果數(shù)據(jù)不是升序排列的，那么用中間值的大小來判斷上下查找區(qū)域就失去了意義，每次二分很可能不是在逼近查找值而是遠(yuǎn)離查找值！

第二，不升序排列就很大可能找不到正確值。

譬如下圖。

查找區(qū)域的數(shù)據(jù)沒有采用升序排列。經(jīng)過4次二分法查找，來到最后一個(gè)值13000，已知13000大于10000，此時(shí)就觸底向上，取首位小于等于查找值的值，即7000，然后返回級(jí)別F。

這就是日常使用LOOKUP二分法進(jìn)行查找，找不到正確值的原因——數(shù)據(jù)區(qū)域沒有升序排列。

VLOOKUP作為LOOKUP的改進(jìn)版，在模糊查找中保留了LOOKUP的特性——用二分法查找。因此當(dāng)用VLOOKUP進(jìn)行區(qū)間查找時(shí)，必須要保持?jǐn)?shù)據(jù)升序排列。

二、二分法查找規(guī)律

通過上面案例，大家對(duì)二分法已經(jīng)有了初步的了解。下面我們細(xì)說二分法的4條查找規(guī)律。

①升序排列是獲得正確值前提。

②大往上小往下查。

③等值向下查，取連續(xù)等值末位數(shù)。

④觸底向上查，取首位小于等于查找值的數(shù)。

注：①上下針對(duì)的是同列查找，即豎向查找；如果同行查找，即橫向查找，則是左右。LOOKUP橫向豎向均可查找。

②不管是否升序排列，二分法都嚴(yán)格遵守②③④的運(yùn)作規(guī)則去取值。

1.第一條和第二條二分規(guī)律

數(shù)據(jù)升序排列在前面已經(jīng)說明，不再贅述。

大往上小往下查：中間值小于查找值，向下（或向右）繼續(xù)二分；反之，向上或向左繼續(xù)二分。

2.第三條二分規(guī)律：等值向下查，取連續(xù)等值末位數(shù)

指的是中間值等于查找值時(shí)，將含中間值在內(nèi)的向下（或向右）計(jì)數(shù)的連續(xù)等值區(qū)域中的最后一位作為查找結(jié)果。

有兩種情況。

（1）向下（或向右）計(jì)數(shù)的連續(xù)等值區(qū)域只包含中間值一個(gè)，那中間值就是查找結(jié)果

譬如下方：第二個(gè)中間值等于查找值11000，由于向下等于查找值的連續(xù)區(qū)域只包括中間值一個(gè)，所以中間值就是查找結(jié)果。

（2）向下（或向右）計(jì)數(shù)的連續(xù)等值區(qū)域只包含多個(gè)值，那最后一個(gè)就是查找結(jié)果

譬如下方：第二個(gè)中間值等于查找值9，由于向下等于查找值的連續(xù)區(qū)域有3個(gè)值，所以第3個(gè)9是查找結(jié)果。

從上圖還可以看出一個(gè)區(qū)別：當(dāng)有多個(gè)值符合查找條件，VLOOKUP總是查找到第一個(gè)符合條件的值；而LOOKUP總是查找到最后一個(gè)符合條件的值（連續(xù)等值區(qū)域內(nèi)）。

3.第四條二分規(guī)律：觸底向上查，取首位小于等于查找值的數(shù)

指的是二分到最后一個(gè)數(shù)據(jù)，將含最后一個(gè)數(shù)據(jù)在內(nèi)的向上（或向左）計(jì)數(shù)的區(qū)域中第一個(gè)小于等于查找值的數(shù)據(jù)作為查找結(jié)果。

有三種情況。

（1）最后一個(gè)數(shù)據(jù)小于或者等于查找值，則最后一個(gè)數(shù)據(jù)就是查找結(jié)果

譬如：經(jīng)過3次二分后，得到最后一個(gè)值99，其向上的區(qū)域（截至前方的中間值）包括兩個(gè)數(shù)字，第一個(gè)小于查找值100的就是最后一個(gè)值99。

拓展：

知道了這點(diǎn)，不難理解如果查找值大于查找區(qū)域中所有數(shù)據(jù)，則LOOKUP肯定返回最后一個(gè)值。

這就是高手用LOOKUP“座”字查找最后一個(gè)文本，用極大值查找最后一個(gè)數(shù)字的秘密。

對(duì)包括英文和中文的文本進(jìn)行升序排列，默認(rèn)按照文本首字母從A到Z進(jìn)行編碼排序，先排英文后排中文。中文中做、坐、座等字是編碼很大很靠后的一批字，所以用它們通常能查到最后一條文本。

9E+307是一個(gè)極大數(shù)，近似Excel能處理的最大數(shù)值，所以當(dāng)需要查最后一條數(shù)字的時(shí)候，通常用9E+307來查找。

（2）最后一個(gè)數(shù)據(jù)大于查找值，則從最后一個(gè)數(shù)據(jù)開始向上計(jì)數(shù)的第一個(gè)小于等于查找值的數(shù)據(jù)就是查找結(jié)果

譬如：經(jīng)過3次二分后，得到最后一個(gè)值105，其向上的區(qū)域（截至前方的中間值）包括兩個(gè)數(shù)字，第一個(gè)小于等于查找值100的就是96。

（3）最后一個(gè)數(shù)據(jù)大于查找值，且從最后一個(gè)數(shù)據(jù)開始向上計(jì)數(shù)找不到小于等于查找值的數(shù)據(jù)則返回錯(cuò)誤值

譬如：三次二分后最后一個(gè)值是70，含它在內(nèi)的上方區(qū)域中沒有一個(gè)值小于等于查找值69，所以返回#N/A 錯(cuò)誤值。

拓展：

不難理解如果查找值小于查找區(qū)域中所有數(shù)據(jù)，則LOOKUP肯定返回#N/A 錯(cuò)誤值。

三、奇怪問題：為何只能查找小于或等于查找值的值？

LOOKUP只能查找小于或等于查找值的值，為什么不能查比查找值大的最小值呢？

用一個(gè)生活中大家都玩過的套圈圈游戲來解釋。

你的面前從小到大地?cái)[了許許多多的熊貓（查找區(qū)域），你花了20塊錢，買了一個(gè)圈（查找值），準(zhǔn)備拿著這個(gè)圈，去套熊貓公仔。套之前，老板娘告訴你游戲規(guī)則：圈圈必須完全將玩具容納進(jìn)去，才算成功。

20塊一個(gè)的圈，拿去套熊貓，你最希望能套到哪個(gè)熊貓呢？

根據(jù)游戲規(guī)則，必須將玩具完全容納才算成功。那么你能套中的，只能是比圓圈直徑小的熊貓，極限發(fā)揮，能套中與圓圈直徑相等的熊貓，但根本不可能套中比圓圈直徑大的熊貓！

這就是LOOKUP只能查找小于或等于查找值的值的原因。

四、怎么理解不排序也能精確查找的LOOKUP套路公式？

LOOKUP精確查找的前提是升序排列，但是經(jīng)過前輩們的探索與努力，也寫出了不排序也能實(shí)現(xiàn)精確查找的套路公式：

=LOOKUP（1,0/(查找范圍=查找值),結(jié)果范圍）

譬如下面：

左側(cè)，品種沒有排序，編碼最小的A排在了最末，用LOOKUP常規(guī)查找得不到正確數(shù)據(jù)；

右側(cè)，同樣沒有排序，用套路公式得到了正確數(shù)據(jù)。

這是什么魔法？

其實(shí)這里巧妙利用了LOOKUP函數(shù)的特性——忽略查找區(qū)域中的錯(cuò)誤值。

在下方的表中，我們?cè)O(shè)法把A品種外的所有品種變成了錯(cuò)誤值#DIV/O、#NAME、#N/A等。用LOOKUP常規(guī)查找，得到了正確答案。原因就在于所有錯(cuò)誤被忽略后，就只剩下A，閉著眼睛都能找到它。

套路公式中“0/(查找范圍=查找值)”的作用就是把不符合條件的都變成錯(cuò)誤值#DIV/O，符合條件的變成0。最后在0中查找1，根據(jù)二分法規(guī)律查找到唯一的0，再返回0對(duì)應(yīng)的數(shù)量。這就是魔法！

如果品種列中A品種有多個(gè)，每個(gè)A都變成0并將在忽略錯(cuò)誤后成為一個(gè)連續(xù)區(qū)域，根據(jù)二分法規(guī)律如果查找區(qū)域中 “所有數(shù)據(jù)都小于查找值，則LOOKUP肯定返回最后一個(gè)值”，則獲得最后一個(gè)A的數(shù)量。如下。

好的，以上就是我花了兩天的時(shí)間，給大家整理的關(guān)于LOOKUP二分法的原理。

懂得了這個(gè)原理，就能完全理解和掌握這個(gè)函數(shù)了，可以在多條件查找、逆向查找、橫向查找、區(qū)間查找、簡全稱查找中大膽使用它而不必總依賴VLOOKUP了。

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

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

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

相關(guān)推薦：

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

10種職場人最常用的excel多條件查找方法?。ńㄗh收藏）

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

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

版權(quán)申明：

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