[EXCEL] 多重條件查表法

　
　　相關函數：INDEX() / SUMPRODUCT() / ROW()

　　用 VLOOKUP() 來查表的另外一個缺點，就是只能使用單一條件來查詢，如果我們需要查詢兩個或多個條件同時成立的狀況，VLOOKUP() 就不適用了。例如，我們想要查詢國文和數學成績都不及格的人，就不能直接使用 VLOOKUP()。

##CONTINUE##

　　如上圖，要在 F3 輸入公式

=INDEX($B$1:$B$5, SUMPRODUCT(--($C$2:$C$5<60),--($d$2:$d$5<60),row($b$2:$b$5)), 1)

　　才能正確找出答案。

　　INDEX() 的作用，之前的斗文章已經提過。ROW() 則是傳回儲存格的列數，所以公式的重點便在 SUMPRODUCT() 這個函數上。SUMPRODUCT() 的原意是把陣列的相對元素先相乘，然後再把每個元素加起來，傳回一個總和的值。在這裡，我們則將每個陣列當做是一個條件的比對結果，符合值為 1，不符合值為 0，如果有任何一個條件不符合，會導致乘以 0 的狀況，進而排除這筆記錄。最後只有符合條件的記錄能傳回 ROW()。

　　以上表為例，結果是 C2:C5 {1,0,1,0} * D2:D5 (0,1,1,0} * ROW(B2:B5) {2,3,4,5} = {0,0,1,0}*{2,3,4,5} = {0,0,4,0}，元素相加後即為 4，是正確答案的列數。

　　注意一：公式中的符號 "--" 是用來把比對大小之後的邏輯值 TRUE/FALSE 轉成數字 1/0。不然，SUMPRODUCT() 會一律把邏輯值當做 0，造成運算錯誤。

　　注意二：如果原始資料中有多筆記錄符合所有條件 (多個人同時不及格)，這個公式就不能適用。我們會在以後的文章中討論這種情形要如何處理。

2006-10-31 補充

　　注意三：如果在原始資料中找不到同時符合多個條件的資料，SUMPRODUCT() 會傳回 0，INDEX() 在處理 0 時可能會造成誤判。要避免這種情形，可以用 IF() 來處理這種特別狀況。公式改成

=IF(SUMPRODUCT(...)=0, "找不到資料", INDEX($B$1:$B$5,SUMPRODUCT(...),1) )