'花了5個小時，總結了8個Excel多條件查找的方法，值得收藏'

Excel中的多條件查找，其實不是很難，很多小夥伴都知道查找用lookup、vlookup函數，但具體怎麼使用卻不知所措。

今天跟大家分享多條件查找最常用的8個方法，如果你以前不懂，現在看看這篇文章，絕對可以給你帶來收穫~

下圖是一個學科成績表，我們需要通過左表的姓名和學號兩個條件在右表中查找對應的成績並返回到左表的E列中。

方法一：使用LOOKUP函數。

具體操作步驟如下：

1、選中E2單元格 -- 在編輯欄中輸入公式“=LOOKUP(1,0/(A2=$G$2:$G$11)*(B2=$H$2:$H$11),$I$2:$I$11)”-- 按回車鍵回車，並將公式下拉填充至E11單元格。

2、動圖演示如下。

3、公式解析。

（1）(A2=$G$2:$G$11)*(B2=$H$2:$H$11)：

首先，將A2單元格的內容與G2:G11單元格區域的內容作對比，將B2單元格的內容與H2:H11單元格區域的內容作對比。如果A2單元格的內容與G2:G11單元格區域的內容相等，B2單元格的內容與H2:H11單元格區域的內容相等，則返回TRUE，不相等時，返回FALSE。根據邏輯值TRUE=1，FALSE=0，所以這部分公式得到的結果可能有3種情況：0*1；1*1；1*0。公式A2=$G$2:$G$11返回的結果為{0;0;0;0;0;1;0;0;0;0}，因為只有G6單元格的值與A2相等。公式B2=$H$2:$H$11返回的結果為{0;0;0;0;0;1;0;0;0;0}，因為只有H6單元格的值與B2相等。所以公式(A2=$G$2:$G$11)*(B2=$H$2:$H$11)返回的結果為{0;0;0;0;0;1;0;0;0;0}。

（2）0/(A2=$G$2:$G$11)*(B2=$H$2:$H$11)：

LOOKUP函數，如果要精確查找，第2個參數查找區域必須升序排序，得到的結果才是正確的。但我們這裡沒有升序排序，用到的是LOOKUP函數的二分法原理，用0來除以(A2=$G$2:$G$11)*(B2=$H$2:$H$11)這個公式的結果值，這裡只會產生兩種情況：0/0或0/1。而在除法運算中，被除數不能為0，也就是分母不能為0，所以在Excel中，0/0會得到錯誤值#DIV/0!，而0/1的結果為0。所以該公式返回的結果為{#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;0;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!}。

（3）=LOOKUP(1,0/(A2=$G$2:$G$11)*(B2=$H$2:$H$11),$I$2:$I$11)：

根據第（2）步公式返回的結果{#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;0;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!}，再根據LOOKUP函數的查找原理，忽略錯誤值查找，所以該公式的意思是，找到與1最接近的值，在第（2）步返回的結果數組中，錯誤值被忽略，只有一個0，0<1，因此返回I2:I11單元格範圍內的第6個數據，即I7單元格的內容“68”。

方法二：使用VLOOKUP函數。

具體操作步驟如下：

1、選中E2單元格 -- 在編輯欄中輸入公式“=VLOOKUP(A2&B2,IF({1,0},$G$2:$G$11&$H$2:$H$11,$I$2:$I$11),2,0)”-- 按組合鍵“Ctrl+Shift+Enter”回車 -- 並將公式下拉填充至E11單元格。

2、動圖演示如下。

3、公式解析。

（1）A2&B2：

我們都知道，VLOOKUP函數查找時查找值默認只能有一個條件，我們這裡是多條件查找，所以可以通過文本連接符&將兩個條件連接起來作為新的查找值。新的查找值也就是“姓名學號”。

（2）IF({1,0},$G$2:$G$11&$H$2:$H$11,$I$2:$I$11)：

{1,0}相當於{TRUE,FALSE}。所以該公式就有兩種情況：第一種情況：=IF(1,$G$2:$G$11&$H$2:$H$11,$I$2:$I$11)，這種情況返回G2:G11單元格區域內容和H2:H11單元格區域內容合併後的結果。第二種情況：=IF(0,$G$2:$G$11&$H$2:$H$11,$I$2:$I$11)，這種情況返回I2:I11單元格區域內容。所以{1,0}相當於重新構建了兩列數據，第1列數據是以G2:G11單元格區域內容和H2:H11單元格區域內容合併後的數據，第2列數據是I2:I11單元格區域構建的數據，如下圖所示。

（3）=VLOOKUP(A2&B2,IF({1,0},$G$2:$G$11&$H$2:$H$11,$I$2:$I$11),2,0)：

第一個參數查找值“A2&B2”，要返回的結果值在IF構建的新數據區域中，屬於第2列，所以第3個參數為2，這裡是精確查找，所以第4個參數為0或者FALSE。

方法三：使用OFFSET函數+MATCH函數。

具體操作步驟如下：

1、選中E2單元格 -- 在編輯欄中輸入公式“=OFFSET($I$1,MATCH(A2&B2,$G$2:$G$11&$H$2:$H$11,0),)”-- 按組合鍵“Ctrl+Shift+Enter”回車 -- 並將公式下拉填充至E11單元格。

2、動圖演示如下。

3、公式解析。

（1）MATCH(A2&B2,$G$2:$G$11&$H$2:$H$11,0)：

將A2單元格的姓名與B2單元格的學號通過用文本連接符合並作為新的查找內容，將G列的姓名和H列的學號通過文本連接符合並作為新的查找區域，0表示精確查找。該公式返回的結果為“6”。

（2）=OFFSET($I$1,MATCH(A2&B2,$G$2:$G$11&$H$2:$H$11,0),)：

OFFSET函數是指以指定的單元格引用為參照系，通過給定偏移量得到新的引用。返回對單元格或單元格區域中指定行數和列數的區域的引用。 返回的引用可以是單個單元格或單元格區域。 可以指定要返回的行數和列數。該公式表示以$I$1為參照單元格，通過MATCH查找出來順序作為向下偏移的行數，偏移列數量省略表示不偏移，第三個、第四個參數省略表示只返回一個單元格區域。第（1）步MATCH函數得到的結果為6，所以向下偏移6行時找到“68”。

方法四：使用SUM函數。

具體操作步驟如下：

1、選中E2單元格 -- 在編輯欄中輸入公式“=SUM((A2=$G$2:$G$11)*(B2=$H$2:$H$11)*($I$2:$I$11))”-- 按組合鍵“Ctrl+Shift+Enter”回車 -- 並將公式下拉填充至E11單元格。

2、動圖演示如下。

3、公式解析。

（1）A2=$G$2:$G$11：

判斷A2單元格的內容是否與G2:G11單元格區域的內容相等，如果相等，返回TRUE，否則，返回FALSE。該公式返回一個邏輯值數組{FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE}。

（2）B2=$H$2:$H$11：

判斷B2單元格的內容是否與H2:H11單元格區域的內容相等，如果相等，返回TRUE，否則，返回FALSE。該公式返回一個邏輯值數組{FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE}。

（3）=SUM((A2=$G$2:$G$11)*(B2=$H$2:$H$11)*($I$2:$I$11))：

3個值相乘，只有當前面2個值都為TRUE時，最後的結果才為TRUE，而TRUE=1，FALSE=0，從上面2步中，我們可以看到只有第6個值為TRUE，其餘都為FALSE。再與I2:I11的值相乘，最後通過SUM函數將得到的值相加起來，最後的結果“68”，將公式往下填充，即可得到其他單元格的值。

方法五：使用SUMPRODUCT函數。

具體操作步驟如下：

1、選中E2單元格 -- 在編輯欄中輸入公式“=SUMPRODUCT((A2=$G$2:$G$11)*(B2=$H$2:$H$11)*($I$2:$I$11))”-- 按Enter鍵回車 -- 並將公式下拉填充至E11單元格。

2、動圖演示如下。

3、公式解析。

（1）A2=$G$2:$G$11：

判斷A2單元格的內容是否與G2:G11單元格區域的內容相等，如果相等，返回TRUE，否則，返回FALSE。該公式返回一個邏輯值數組{FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE}。

（2）B2=$H$2:$H$11：

判斷B2單元格的內容是否與H2:H11單元格區域的內容相等，如果相等，返回TRUE，否則，返回FALSE。該公式返回一個邏輯值數組{FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE}。

（3）=SUMPRODUCT((A2=$G$2:$G$11)*(B2=$H$2:$H$11)*($I$2:$I$11))：

3個值相乘，只有當前面2個值都為TRUE時，最後的結果才為TRUE，而TRUE=1，FALSE=0，從上面2步中，我們可以看到只有第6個值為TRUE，其餘都為FALSE。再與I2:I11的值相乘，最後通過SUMPRODUCT函數將每個數組對應元素的值相乘，最後再相加，得到的結果為“68”，將公式往下填充，即可得到其他單元格的值。

方法六：使用MAX函數。

具體操作步驟如下：

1、選中E2單元格 -- 在編輯欄中輸入公式“=MAX((A2=$G$2:$G$11)*(B2=$H$2:$H$11)*($I$2:$I$11))”-- 按組合鍵“Ctrl + Shift +Enter”回車 -- 並將公式下拉填充至E11單元格。

2、動圖演示如下。

3、公式解析。

（1）A2=$G$2:$G$11：

判斷A2單元格的內容是否與G2:G11單元格區域的內容相等，如果相等，返回TRUE，否則，返回FALSE。該公式返回一個邏輯值數組{FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE}。

（2）B2=$H$2:$H$11：

判斷B2單元格的內容是否與H2:H11單元格區域的內容相等，如果相等，返回TRUE，否則，返回FALSE。該公式返回一個邏輯值數組{FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE}。

（3）=MAX((A2=$G$2:$G$11)*(B2=$H$2:$H$11)*($I$2:$I$11))：

將(A2=$G$2:$G$11)*(B2=$H$2:$H$11)*($I$2:$I$11)這3個數組相乘，最後得到的結果為{0;0;0;0;0;68;0;0;0;0}，根據MAX函數的原理，找到一組值中的最大值並返回，很顯然，結果數組中的最大值為“68”，所以返回的結果為68，將公式往下填充，即可得到其他單元格的值。

方法七：使用MIN函數+IF函數。

1、選中E2單元格 -- 在編輯欄中輸入公式“=MIN(IF((A2=$G$2:$G$11)*(B2=$H$2:$H$11),$I$2:$I$11))”-- 按組合鍵“Ctrl + Shift +Enter”回車 -- 並將公式下拉填充至E11單元格。

2、動圖演示如下。

3、公式解析。

（1）(A2=$G$2:$G$11)*(B2=$H$2:$H$11)：

判斷A2單元格的內容是否與G2:G11單元格區域的內容相等，如果相等，返回TRUE，否則，返回FALSE。判斷B2單元格的內容是否與H2:H11單元格區域的內容相等，如果相等，返回TRUE，否則，返回FALSE。該公式返回一個0和1組成的數組{0;0;0;0;0;1;0;0;0;0}。

（2）IF((A2=$G$2:$G$11)*(B2=$H$2:$H$11),$I$2:$I$11)：

如果(A2=$G$2:$G$11)*(B2=$H$2:$H$11)為TRUE，返回$I$2:$I$11單元格區域的內容，如果為FALSE，返回空。所以該公式返回的結果為{FALSE;FALSE;FALSE;FALSE;FALSE;68;FALSE;FALSE;FALSE;FALSE}。

（3）=MIN(IF((A2=$G$2:$G$11)*(B2=$H$2:$H$11),$I$2:$I$11))：

MIN函數是返回一組值中的最小值，空單元格、邏輯值和文本將被忽略。由第（2）步可知，只有68是數值，所以返回的結果就是68，將公式往下填充，即可得到其他單元格的值。

方法八：使用INDEX函數+MATCH函數。

1、選中E2單元格 -- 在編輯欄中輸入公式“=INDEX($I$2:$I$11,MATCH(A2&B2,$G$2:$G$11&$H$2:$H$11,0))”-- 按組合鍵“Ctrl+Shift+Enter”鍵回車 -- 並將公式下拉填充至E11單元格。

2、動圖演示如下。

3、公式解析。

（1）$I$2:$I$11：

要返回的結果所在的單元格區域。該公式得到一組數組{80;71;82;84;70;68;90;74;70;89}。

（2）MATCH(A2&B2,$G$2:$G$11&$H$2:$H$11,0)：

將A2單元格的姓名與B2單元格的學號通過用文本連接符合並作為新的查找內容，將G列的姓名和H列的學號通過文本連接符合並作為新的查找區域，0表示精確查找。該公式返回的結果為“6”。

（3）=INDEX($I$2:$I$11,MATCH(A2&B2,$G$2:$G$11&$H$2:$H$11,0))：

INDEX函數的作用是：返回表或區域中的值或值的引用。上述公式由第（1）步和第（2）步得到的結果，可將公式寫成=INDEX({80;71;82;84;70;68;90;74;70;89},6)。6是行號，也就是從在{80;71;82;84;70;68;90;74;70;89}這組值中返回第6行單元格值的引用，所以為68，將公式往下填充，即可得到其他單元格的值。

以上就是多條件查找的8中方法，不知不覺本文也寫了6000左右字，希望親可以轉發和點贊鼓勵支持一下。如有不懂之處，可在評論區留言！更多常用Excel函數組合，請持續關注本頭條號！

您的每一份讚賞、轉發、評論、點贊、收藏都將成為我們寫出更多優質教程的動力！感激不盡！