Excel索引與多個標準匹配

首頁

專題

excel

Excel索引與多個標準匹配 - 公式示例

William Shakespeare

Apr 02, 2025 am 09:29 AM

本教程顯示瞭如何使用索引和匹配以及其他幾種方式在Excel中使用多個標準查找。

儘管Microsoft Excel為垂直和水平查找提供了特殊功能，但專家用戶通常會用索引匹配替換它們，這在許多方面都優於vlookup和hlookup。除其他外，它可以在列和行中查找兩個或多個標準。本教程詳細說明了語法和內部力學，以便您可以輕鬆地調整特定需求的公式。為了使示例更容易遵循，歡迎您下載我們的示例工作簿。

在使用大型數據庫時，您有時可能會發現自己需要找到某些東西，但沒有唯一的搜索標識符。在這種情況下，有幾種條件的查找是唯一的解決方案。

要根據單獨列中的多個標準查找值，請使用此通用公式：

{= index（ return_range ，match（1，（ criteria1 = range1 ） *（ criteria2 = range2 ） *（…），0）}}}}

在哪裡：

return_range是返回值的範圍。
標準1 ，標準2 ，…是要滿足的條件。
Range1 ， Range2 ，…是應測試相應標準的範圍。

重要說明！這是一個數組公式，必須在Ctrl Shift Enter中完成。這將使您的公式包含在{curly brokkets}中，這是Excel中數組公式的視覺跡象。不要嘗試手動鍵入牙套，那將行不通！

公式是標誌性索引匹配的高級版本，該版本根據單個標準返回匹配項。為了評估多個標準，我們使用在數組公式中用作和運算符的乘法操作。下面，您將找到一個現實生活中的示例和邏輯的詳細說明。

提示。在Excel 365和2021中，您可以使用具有多個標準的Xlookup公式。

索引與多個標準匹配 - 公式示例

在此示例中，我們將在其自己的行上使用所謂的“扁平文件”格式中的表格（在我們的情況下為月度數字）。我們的目標是在特定地區和月份中檢索某些項目的銷售數字。

借助以下單元格中的源數據和標準：

return_range （銷售）-D2：D13
標準1 （目標區域）-G1
標準2 （目標月）-G2
標準3 （目標項目）-G3
Range1 （區域）-A2：A13
範圍2 （月）-B2：B13
範圍3 （項目）-C2：C13

該公式採用以下形狀：

=INDEX(D2:D13, MATCH(1, (G1=A2:A13) * (G2=B2:B13) * (G3=C2:C13), 0))

輸入公式，例如在G4中，通過按CTRL Shift Enter來完成它，您將獲得以下結果：

Excel索引與多個標準匹配 - 公式示例

該公式如何工作

最棘手的部分是匹配功能，所以讓我們首先弄清楚：

MATCH(1, (G1=A2:A13) * (G2=B2:B13) * (G3=C2:C13), 0))

您可能還記得，匹配（lookup_value，lookup_array，[match_type]）在查找數組中搜索查找值，並在數組中返回該值的相對位置。

在我們的公式中，論點如下：

Lookup_value ：1
Lookup_array ：（ g1 = a2：a13） *（g2 = b2：b13） *（g3 = c2：c13）
match_type ：0

第1^個參數是晶體清晰的 - 函數搜索數字1。設置為0的3 ^rd參數表示“精確匹配”，即公式返回第一個發現的值，該值完全等於查找值。

問題是 - 為什麼我們要搜索“ 1”？要獲取答案，讓我們仔細查看查找陣列，在該陣列中，我們將每個標準與相應的範圍進行比較：G1中的目標區域與所有區域（A2：A13）（A2：A13），G2中的目標月對所有月份（B2：B13）和G3中的目標項目（C2：C2：C13）。中間結果是3個真實和錯誤的陣列，而true表示滿足已測試條件的值。要可視化這一點，您可以在公式中選擇單個表達式，然後按F9鍵查看每個表達式評估的內容：

Excel索引與多個標準匹配 - 公式示例

乘法操作分別將真實值和錯誤值分別轉換為1和0：

{1;1;1;1;1;1;0;0;0;0;0;0} * {0;0;1;1;0;0;0;0;1;1;0;0} * {1;0;1;0;1;0;1;0;1;0;1;0}

而且由於乘以0總是給出0，因此結果數組僅在滿足所有標準的行中只有1個：

{0;0;1;0;0;0;0;0;0;0;0;0}

上面的數組轉到匹配的Lookup_array參數。使用Lookup_value為1，該函數返回所有標準為真的行的相對位置（在我們的情況下第3行）。如果數組中有幾個1，則返回第一個位置。

匹配返回的數字直接轉到索引的row_num參數（array，row_num，[column_num]）函數：

=INDEX(D2:D13, 3)

它產生的結果為$ 115，這是D2：D13數組中的3 ^rd值。

具有多個標準的非陣列索引匹配公式

上一個示例中討論的陣列公式適合經驗豐富的用戶。但是，如果您正在為他人建立一個公式，並且某人不知道數組功能，他們可能會無意中打破它。例如，用戶可以單擊您的公式檢查它，然後按Enter而不是Ctrl Shift Enter 。在這種情況下，明智的做法是避免陣列並使用更具防彈性的常規公式：

index（ return_range ，match（1，index（（ criteria1 = range1 ） *（ criteria2 = range2 ） *（..），0，1），0），0））

對於我們的示例數據集，公式如下：

=INDEX(D2:D13, MATCH(1, INDEX((G1=A2:A13) * (G2=B2:B13) * (G3=C2:C13), 0, 1), 0))

Excel索引與多個標準匹配 - 公式示例

該公式如何工作

由於索引函數可以本地處理數組，因此我們添加了另一個索引來處理1和0的數組，該數組是通過乘以兩個或多個true/false數組創建的。第二個索引配置為0 row_num參數，以使公式返回整個列數組而不是單個值。由於它是一個單列數組，因此我們可以安全地為Column_num提供1：

INDEX({0;0;1;0;0;0;0;0;0;0;0;0}, 0, 1) returns {0;0;1;0;0;0;0;0;0;0;0;0}

此數組傳遞給匹配功能：

MATCH(1, {0;0;1;0;0;0;0;0;0;0;0;0}, 0)

匹配找到所有標準為真的行（更準確地說，是指定數組中該行的相對位置），並將該編號傳遞給第一個索引的Row_num參數：

=INDEX(D2:D13, 3)

索引與行和列中的多個標準匹配

此示例顯示瞭如何通過在行和列中測試兩個或多個標準來執行查找。實際上，這是一個更複雜的情況，即帶有多個標頭行的所謂“矩陣查找”或“雙向查找”。

這是一行和列中有多個標準的通用索引匹配公式：

{= index（ table_array ，match（ vlookup_value ， lookup_column ，0），match（ hlookup_value1 ＆ hlookup_value2 ， lookup_row1 ＆lookup_row1＆ lookup_row2，0 ）}）}））}）

在哪裡：

table_array-在內部搜索的地圖或區域，即所有數據值不包括列和行標頭。

vlookup_value-您在列中垂直尋找的值。

Lookup_column-通常要搜索的列範圍，通常是行標頭。

hlookup_value1，hlookup_value2，… - 在行中水平尋找的值。

Lookup_row1，Lookup_row2，… - 行搜索範圍，通常是列標題。

重要說明！為了使公式正確工作，必須將其作為帶有Ctrl Shift Enter的數組公式輸入。

它是經典的雙向查找公式的變體，該公式在某個行和列的交點處搜索一個值。不同之處在於，您將幾個Hlookup值和範圍串聯以評估多個列標題。為了更好地理解邏輯，請考慮以下示例。