excel中的lookup函數究竟該怎么用？如何才能準確理解它的用法？

千彩一工作室 2019-05-24

展開全文

在日常工作中，excel的查找函數可以稱得上是函數的精髓之一，因為查找函數單獨使用就可以實現很多日常操作需要，更別說很多復雜的函數往往也會嵌套查找函數使用，常用的查找函數有vlookup、index—match、lookup函數等，這些函數非常相似，有些查找選擇這些函數任意一個都可以完成，今天小編給大家介紹一下lookup函數的幾種用法，這個函數非常靈活，可以在很多種情況下運用。

本文概要

一：基本正向查找與反向查找

其實查找本來沒有必要分成正向查找或者反向查找，因為vlookup函數用的人實在太多了，vlookup函數進行正向查找比較簡單，反向查找稍顯復雜，所以正向查找和反向查找會做出區(qū)分。

但是在lookup函數中，正向查找和反向查找的公式完全一樣，即公式=lookup（1,0/（條件區(qū)域=條件）,返回結果區(qū)域）。這個格式是lookup函數常用的固定套路，可能比較難以理解，下面就以案例介紹這個函數的含義

以下圖中根據姓名查找成績?yōu)槔?strong>E2單元格輸入的函數=LOOKUP(1,0/($A$2:$A$10=D2),$B$2:$B$10)，

在這個函數中，第二個參數的分母部分是($A$2:$A$10=D2)，表示判斷D2單元格是否等于A2到A10單元格，如果等于返回的結果是true，否則返回的結果是false，此處返回的結果是{FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE}，即第四個判斷為true。

在0/($A$2:$A$10=D2)中，其實true可以看做是1，false可以看做是0，計算結果只有第四個返回的值為0，其他為錯誤值，因為0作為分母無意義，此處返回的結果是{#DIV/0!;#DIV/0!;#DIV/0!;0;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!}

在lookup函數查找中，查找的結果會忽略錯誤值，而第二個參數只有第四個為0，不是錯誤值，所以第一個參數1在第二個參數中查找，返回的查找結果是第四個數，即“趙云”，第三個參數$B$2:$B$10為返回的結果，即最終計算結果返回“趙云”對應的成績。

查找完成后，向下拖動即可填充公式，同理，只要成績不唯一（此處只為舉例），根據成績反向查找姓名公式含義不變。

二：多條件查找

通過上述基本查找的介紹，相信大家對lookup函數的公式含義有一定的了解，其實在多條件查找中，仍然使用的是與基本查找相同的套路，即函數公式=lookup（1,0/（（條件區(qū)域1=條件1）*（條件區(qū)域2=條件2）），返回結果區(qū)域）。

下圖中根據區(qū)域和產品型號查找銷量，那么在H2單元格輸入的函數公式為=LOOKUP(1,0/(($A$2:$A$21=F2)*($B$2:$B$21=G2)),$C$2:$C$21)，這個函數中第二個參數的分母部分發(fā)生變化，用乘號連接表示需要同時滿足這兩個條件，乘號前面和后面的條件判斷結果false可以看作為0，true可以看作為1，所以兩者相乘返回的結果就是1或者0。最后分母部分返回的結果是{0;0;0;0;0;1;0;0;0;0;0;0;0;0;0;0;0;0;0;0}，其他參數的含義就和上面介紹的一樣了。

三：模糊查找

在下圖中，EF兩列為評價標準和評價類型，我們要根據EF列的標準，在C列中顯示B列銷量數據的評價類型，該如何設置公式呢？

這種查找的基本函數=lookup（查找依據,查找區(qū)域,返回結果區(qū)域）。所以我們在C2單元格輸入的公式為=LOOKUP(B2,$E$2:$E$7,$F$2:$F$7)，其實這個函數可以說是lookup函數的基本用法，lookup函數在查找中，如果查找不到準確的值，那么就會返回到小于查找值的最大值對應的結果，前提是對查找范圍進行升序排序，即下圖中E列數據按從小到大順序排列。

在C2單元格中，根據B2單元格的內容在E2到E7單元格中進行查找，因為520在查找區(qū)域中沒有準確的查找依據，所以返回查找的依據是500，最后返回第三個參數對應的結果，即“一類”。然后向下拖動即可填充公式。

四：查找最后一次出現的記錄

在下圖中，如何查找“一班”、“二班”、“三班”對應的最后一個名字及成績呢？

這里又用到了lookup函數的基本查找了，即F2單元格的公式=LOOKUP(1,0/($A$2:$A$10=E2),$B$2:$B$10)。第二個參數的分母($A$2:$A$10=E2)中的判斷結果有兩個會返回true，所以0/($A$2:$A$10=E2)返回的結果為{#DIV/0!;#DIV/0!;0;#DIV/0!;#DIV/0!;0;#DIV/0!;#DIV/0!;#DIV/0!}，而lookup函數查找不到滿足條件的值時，會忽略錯誤并返回最后一個值。所以此處會查找到班級對應的最后一個姓名和成績。

五：根據簡稱在全稱中查找

在下圖中，D列是查找依據，AB兩列是查找數據源，但是此處查找依據是簡稱，其他查找函數就不好實現了，利用lookup函數仍然非常容易。

這種查找的基本公式仍然為=lookup（查找依據,查找區(qū)域,返回結果區(qū)域）。所以在E2單元格中輸入公式=LOOKUP(9^9,FIND(D2,$A$2:$A$5),$B$2:$B$5)即可。第二個參數FIND(D2,$A$2:$A$5)表示D2單元格在A2到A5單元格進行查找，返回的結果為{#VALUE!;#VALUE!;5;#VALUE!}，因為只有在第三個單元格可以查到“會計”并且返回的結果為5，即“會計”在“中級財務會計”中第5個字符開始。而其他結果都為錯誤值，所以9^9查到不到準確結果，忽略錯誤值后，只會返回5對應的成績。

這里第一個參數9^9只是為了保證準確，此例中A列的單元格最多為8個字符，find函數返回的最大結果也不可能超過8，所以此處第一個參數輸入一個9也能返回正確的結果。

六：根據全稱在簡稱中查找

和上面情況相反，如果查找的數據源是簡稱，我們要根據全稱查找對應的內容，該如何操作呢？

此時在E2單元格輸入的公式為=LOOKUP(1,0/FIND($A$2:$A$5,D2),$B$2:$B$5)，這個函數和上面的根據簡稱查找全稱十分相似，主要差異是在第二個參數，第二個參數的分母函數為FIND($A$2:$A$5,D2)，即查找數據源中的簡稱在查找依據中的位置，返回的結果是{#VALUE!;3;#VALUE!;#VALUE!}，忽略錯誤值后，會返回第二個參數對應的值。