如何創建具有多個條件的 Excel 查找公式

須知

• 首先，創建一個 INDEX 函數，然後通過輸入 Lookup_value 參數啟動嵌套的 MATCH 函數。
• 接下來，添加 Lookup_array 參數，後跟 Match_type 參數，然後指定列範圍。
• 然後，按Ctrl + Shift + Enter將嵌套函數轉換為數組公式。最後，將搜索詞添加到工作表中。

本文介紹如何在 Excel 中創建使用多個條件的查找公式，以使用數組公式在數據庫或數據表中查找信息。數組公式涉及將 MATCH 函數嵌套在 INDEX 函數中。信息涵蓋 Excel for Microsoft 365、Excel 2019、Excel 2016、Excel 2013、Excel 2010 和 Excel for Mac。

按照教程進行操作

要按照本教程中的步驟進行操作，請將示例數據輸入到以下單元格中，如下圖所示。第 3 行和第 4 行留空以容納本教程中創建的數組公式。（請注意，本教程不包括圖像中看到的格式。）

• 在單元格 D1 到 F2 中輸入最高範圍的數據。
• 在單元格 D5 到 F11 中輸入第二個範圍。

在 Excel 中創建一個 INDEX 函數

INDEX 函數是 Excel 中為數不多的具有多種形式的函數之一。該函數有一個數組形式和一個參考形式。數組形式從數據庫或數據表中返回數據。參考表格給出表格中數據的單元格參考或位置。

在本教程中，數組表單用於查找鈦部件供應商的名稱，而不是數據庫中對該供應商的單元格引用。

按照以下步驟創建 INDEX 函數：

1. 選擇單元格F3使其成為活動單元格。此單元格是將輸入嵌套函數的位置。
2. 轉到公式。

   

3. 選擇Lookup & Reference打開函數下拉列表。
4. 選擇INDEX以打開“選擇參數”對話框。
5. 選擇array,row_num,column_num。
6. 選擇“確定”以打開“函數參數”對話框。在 Excel for Mac 中，公式生成器打開。
7. 將光標置於數組文本框中。
8. 突出顯示工作表中的單元格D6到F11，以將範圍輸入到對話框中。
   使“函數參數”對話框保持打開狀態。公式還沒有完成。您將完成以下說明中的公式。
   

啟動嵌套匹配函數

將一個函數嵌套在另一個函數中時，無法打開第二個或嵌套的函數的公式生成器來輸入必要的參數。嵌套函數必須作為第一個函數的參數之一輸入。

手動輸入函數時，函數的參數之間用逗號分隔。

進入嵌套 MATCH 函數的第一步是輸入 Lookup_value 參數。Lookup_value 是要在數據庫中匹配的搜索詞的位置或單元格引用。

Lookup_value 只接受一個搜索條件或術語。要搜索多個條件，請使用與符號 (&) 連接或加入兩個或多個單元格引用來擴展 Lookup_value。

1. 在Function Arguments對話框中，將光標置於Row_num文本框中。
2. 輸入匹配（。
3. 選擇單元格D3將該單元格引用輸入到對話框中。
4. 在單元格引用D3後輸入&（與號）以添加第二個單元格引用。
5. 選擇單元格E3輸入第二個單元格引用。
6. 在單元格引用 E3 後輸入, （逗號）以完成 MATCH 函數的 Lookup_value 參數的輸入。

   
   在本教程的最後一步，Lookup_values 將被輸入到工作表的單元格 D3 和 E3 中。

完成嵌套 MATCH 函數

此步驟包括為嵌套 MATCH 函數添加 Lookup_array 參數。Lookup_array 是 MATCH 函數搜索以查找在本教程上一步中添加的 Lookup_value 參數的單元格範圍。

由於在 Lookup_array 參數中標識了兩個搜索字段，因此必須對 Lookup_array 執行相同的操作。MATCH 函數僅為指定的每個術語搜索一個數組。要輸入多個數組，請使用與號將數組連接在一起。

1. 將光標放在Row_num文本框中數據的末尾。光標出現在當前條目末尾的逗號之後。
2. 突出顯示工作表中的單元格D6到D11以輸入範圍。該範圍是函數搜索的第一個數組。
3. 在單元格引用D6:D11後輸入&（和號）。該符號使函數搜索兩個數組。
4. 突出顯示工作表中的單元格E6到E11以輸入範圍。該範圍是函數搜索的第二個數組。
5. 在單元格引用E3後輸入,（逗號）以完成 MATCH 函數的 Lookup_array 參數的輸入。

   

6. 使對話框保持打開狀態以進行教程的下一步。

添加 MATCH 類型參數

MATCH 函數的第三個也是最後一個參數 是 Match_type 參數。此參數告訴 Excel 如何將 Lookup_value 與 Lookup_array 中的值相匹配。可用的選項是 1、0 或 -1。

此參數是可選的。如果省略，函數使用默認值 1。

• 如果 Match_type = 1 或被省略，則 MATCH 查找小於或等於 Lookup_value 的最大值。Lookup_array 數據必須按升序排序。
• 如果 Match_type = 0，則 MATCH 查找等於 Lookup_value 的第一個值。Lookup_array 數據可以按任何順序排序。
• 如果 Match_type = -1，則 MATCH 查找大於或等於 Lookup_value 的最小值。Lookup_array 數據必須按降序排列。

在INDEX函數中Row_num行上一步輸入的逗號後輸入以下步驟：

1. 在Row_num文本框中的逗號後輸入0（零） 。此數字導致嵌套函數返回與單元格 D3 和 E3 中輸入的術語完全匹配的結果。
2. 輸入)（右圓括號）以完成 MATCH 功能。

   

3. 使對話框保持打開狀態以進行教程的下一步。

完成 INDEX 函數

MATCH 函數完成。是時候移動到對話框的 Column_num 文本框並輸入 INDEX 函數的最後一個參數了。此參數告訴 Excel 列號在 D6 到 F11 範圍內。這個範圍是它找到函數返回的信息的地方。在這種情況下，鈦部件的供應商。

1. 將光標置於Column_num文本框中。
2. 輸入3（數字三）。此數字指示公式在 D6 到 F11 範圍的第三列中查找數據。

   

3. 使對話框保持打開狀態以進行教程的下一步。

創建數組公式

在關閉對話框之前，將嵌套函數轉換為數組公式。該數組允許函數在數據表中搜索多個術語。在本教程中，匹配了兩個術語：第 1 列中的 Widgets 和第 2 列中的 Titanium。

要在 Excel 中創建數組公式，請同時按CTRL、SHIFT和ENTER鍵。一旦按下，該函數就會被花括號包圍，表示該函數現在是一個數組。

1. 選擇確定關閉對話框。在 Excel for Mac 中，選擇“完成”。
2. 選擇單元格F3以查看公式，然後將光標置於公式欄中公式的末尾。
3. 要將公式轉換為數組，請按CTRL + SHIFT + ENTER。
4. #N/A 錯誤出現在單元格 F3 中。這是輸入函數的單元格。
5. #N/A 錯誤出現在單元格 F3 中，因為單元格 D3 和 E3 為空白。D3 和​​ E3 是函數查找 Lookup_value 的單元格。將數據添加到這兩個單元格後，錯誤將替換為數據庫中的信息。

   

添加搜索條件

最後一步是將搜索詞添加到工作表中。此步驟與第 1 列中的 Widgets 和第 2 列中的 Titanium 相匹配。

如果公式在數據庫的相應列中找到兩個術語的匹配項，它會返回第三列中的值。

1. 選擇單元格D3。
2. 輸入小部件。
3. 選擇單元格E3。
4. 鍵入Titanium，然後按Enter 鍵。
5. 供應商名稱 Widgets Inc. 出現在單元格 F3 中。這是列出的唯一一家銷售 Titanium Widgets 的供應商。
6. 選擇單元格F3。該函數出現在工作表上方的公式欄中。在此示例中，只有一家鈦部件供應商。如果有多個供應商，則函數返回數據庫中列在第一位的供應商。
   
{=INDEX(D6:F11,MATCH(D3&E3,D6:D11&E6:E11,0),3)}