excel多條件查找方法:lookup、vlookup、indexmatch多條件查找

 辦公微生活 2019-06-01 13:37

本文分享三種excel多條件查找函數方法，分別是：lookup多條件查詢、vlookup多條件查找、indexmatch多條件查找。

下面通過一個實例跟大家分享一下常用的3種excel多條件查找函數。

下表是某電商公司的客戶投訴表，現在需要通過A表中的客戶姓名與地區兩個條件來查詢B表中的產品型號，返回到A表的E列中。

下面是三種excel雙條件查找返回的方法，依次來看：

第一 excellookup多條件查詢

函數公式：=LOOKUP(1,0/(A3=$I$3:$I$19)*(B3=$J$3:$J$19),$K$3:$K$19)

公式解析：首先通過A3單元格與B表I列數據做對比，同時用B3單元格與B表J列信息做對比。

在excel中如果兩個單元格對比，相等則返回TRUE，在四則運算中用1表示。如果不相等則返回FALSE，使用0表示。

那麼(A3=$I$3:$I$19)*(B3=$J$3:$J$19)這部分運算的結果就只有0或者1兩種情況，因為只有0*1、1*1、1*0這三種情況。

按照二分法原理，lookup函數會在二分位處查找符合條件的數據。大家都知道lookup函數想要精準查找那麼這組數值必須要升序，但實際上這組數據運算結果0和1的順序是混亂的。

所以就想到了用0來除以0和1的方式來區分。由於分母不能為0，所以0/0返回的是錯誤，0/1返回的結果為0。Lookup函數在查找的時候是忽略錯誤的，所以只有數據運算結果為1的公式滿足條件。

那麼我們就很好理解0/(A3=$I$3:$I$19)*(B3=$J$3:$J$19)的目的就是將正確結果用0表示，其他的變成錯誤值，利用函數查找忽略錯誤這個特點完成查找。

總結：本函數由於使用了二分法原理查找，所以如果數據量較大時運算會很慢。

第二 vlookup多條件查找

vlookup函數是我們最常用的函數，vlookup函數主要用於垂直方向上向右查找。如下圖：

使用G2單元格在A列中查找，如果查找到對應單元格則返回A列向右第二列的數據。簡而言之：=VLOOKUP(查找什麼，在哪查找，從條件所在列算起找到後返回對應的第幾列數據，精確或模糊查找)。

那vlookup如何才能完成多條件查詢呢？。

還以客戶投訴表為例，按照姓名&地區來匹配產品型號返回到E裡中。

其實我們是可以將A、B兩表中插入輔助列，將姓名和地區都合併到一個單元格中然後使用vlookup來完成。

但是插入2個輔助列後整個表列數發生變動，在工作中往往單元格中有很多公式，如果列數發生變化將直接導致表格中函數公式運算結果錯誤。所以添加輔助列的方式雖然簡單，但不是最好的方式。

那麼不用輔助列如何才能完成多條件查詢呢？

首先我們查找值合併很簡單，輸入函數vlookup時第一個參數可以寫成A3&B3，即可將A3、B3兩個單元格內容合併，作為查找值。

現在問題查找區域也需要做合併。

如果把兩列內容合併在一起，可輸入公式=H2:H19&I2:I19，按ctrl+shift+回車生成結果，然後下拉公式，這樣兩個條件就變成了一個。

接下來通過IF函數提取對應的J列數據，可輸入公式=IF({0,1},H2:H19&I2:I19,J2:J19)，按ctrl+shift+回車生成結果，然後下拉公式，{0,1}表示邏輯值{FALSE,TRUE}。

下面我們詳細來解析一下:

首先在excel中0表示錯誤，1以及其他所有數值表示正確。如下表示例：

通過上面的例子我看到如果IF判斷0則返回錯誤，判斷1則返回正確。

現在我們可以將公式拆分為以下兩種情況：

IF（0, H2:H19&I2:I19,J2:J19），0表示FALSE，所以只能返回J列數據。

IF（1, H2:H19&I2:I19,J2:J19），1表示TRUE，所以只能返回H列和I列合併結果。

那麼IF({0,1},H2:H19&I2:I19,J2:J19)怎麼理解呢？

既然是數組公式，那麼可以將它理解為同時返回兩組數據，0對應的是J2:J19，1對應的H2:H19&I2:I19，構建了兩列數據。

最後我們使用vlookup函數完成嵌套，=VLOOKUP(A3&B3,IF({1,0},H3:H20&I3:I20,J3:J20),2,0)，這裡我們就可以理解為用A3&B3在H3:H20&I3:I20中查找對應J3:J20中的數據。因為公式中IF({1,0},H3:H20&I3:I20,J3:J20)返回的順序是先返回H3:H20&I3:I20再返回J3:J20。

注意：很多人不明白為什麼嵌套的時候IF第一參數又變成了{1,0}，因為這裡我們需要返回的是H和I合併結果作為查找區域。PS：所有數組公式完成輸入後要使用數組三鍵ctrl+shift+ener來返回運算結果！

這樣我們不用輔助列也能通過vlookup函數完成多條件查詢。

第三 OFFSET+MATCH函數公式

很多excel高手都知道offset可以當vlookup函數使用，但職場新人大多都不了解。

下面舉例跟大家分享一下通過offset函數完成多條件查詢。

函數公式：{=OFFSET($J$2,MATCH(A3&B3,$H$3:$H$19&$I$3:$I$19,0),)}

公式解析：

完成多條件查詢第一步先要確定A表中姓名&地區合併後對應在B表中姓名&地區的順序。這裡我們通過MATCH來完成，我們用個簡單的例子說明。

=MATCH(A2,E:E,0)表示使用A2單元格在E列中查找，0表示精確查找、1小於、-1大於，通常情況下都是精確查找。

MATCH(A3&B3,$H$3:$H$19&$I$3:$I$19,0)表示將A3與B3合併作為查找內容，H列和I列合併作為查找區域，0表示精確查找。

確定順序後我們通過OFFSET函數以順序數據作為偏移行數返回對應數值。

OFFSET函數的功能是以指定的單元格引用為參照系，通過給定偏移量得到新的引用。

返回的引用可以為一個單元格或區域。並可以指定返回的行數或列數。Reference 作為偏移量參照系的引用區域。Reference 必須為對單元格或相連單元格區域的引用；否則，函數 OFFSET 返回錯誤值#VALUE!。

=OFFSET(J2,1,0,1,1)表示以J2單元格作為參照物向下偏移1行，向右偏移0列，返回1行1列數據區域。

=OFFSET($J$2,MATCH(A3&B3,$H$3:$H$19&$I$3:$I$19,0),)表示以$J$2為參照單元格，通過MATCH查找出來順序作為向下偏移的行數，偏移列數量省略表示不偏移，第三個、第四個參數省略表示只返回一個單元格區域。