如何在 Excel 中查找多個值：循序漸進

在 Microsoft Excel 中處理大量數據時，查找符合多個條件的多個值可能具有挑戰性。某些內置函數（例如 VLOOKUP）最初設計用於處理單個值。

可以組合多個Excel 函數來查找多個值。它們包括 VLOOKUP、INDEX、MATCH 和 IF 函數。當前版本的 Excel 可以使用動態數組，而舊版本使用數組公式。

本文將向您展示如何在公式中使用這些函數來查找數據中的多個值。

我們走吧！

目錄

• 如何對多個值使用 VLOOKUP
  • 什麼是數組公式？
  • VLOOKUP 示例
  • 最新版本的 Excel
  • 舊版本
  • 精確匹配與近似匹配
  • 更多關於 VLOOKUP
• 如何使用 INDEX 和 MATCH 查找多個值
  • 如何在 Excel 365 中同時使用 INDEX 和 MATCH
  • 使用舊版本的 Excel
• 如何使用 IF 函數查找多個值
  • 當前版本的 Excel
  • 舊版本的 Excel
• 如何對多個條件使用 SUMPRODUCT
• 查找函數的 3 個常見錯誤
  • 1. #N/A 錯誤
  • 2. #REF！錯誤
  • 3.循環錯誤
• 如何將 IFERROR 與查找函數一起使用
• 7 種高級查找技術
  • 1.相對位置查找
  • 2. 小功能
  • 3.行函數
  • 4.過濾功能
  • 5. 輔助列
  • 6. 動態數組
  • 7. 電源查詢
• 最後的想法

如何對多個值使用 VLOOKUP

VLOOKUP函數通常用於查找一系列數據中的單個值。但是，您也可以使用此查找公式在 Excel 中查找多個匹配項。

默認情況下，它只會返回它找到的第一個匹配值。但是，您可以使用數組公式修改函數以返回多個值。

什麼是數組公式？

數組公式是可以對數據數組進行計算的公式。之所以稱為數組公式，是因為它可以返回結果數組，而不是單個值。

創建數組公式時有幾個簡單的步驟：

1. 選擇要搜索的單元格範圍。

2. 在公式欄中輸入公式。

3. 按 Ctrl + Shift + Enter 完成它。

數組公式的語法類似於常規公式的語法，但它在公式兩邊包含花括號 {}。花括號表示該公式是一個數組公式，它將返回一個值數組。

本文中的示例將向您展示如何正確使用數組公式。

VLOOKUP 示例

我們的示例在工作表的 A 列中有五個項目：

• 蘋果

• 香蕉

• 麵包

• 胡蘿蔔

• 櫻桃

任務是檢查三個特定的水果是否在此列表中：蘋果、香蕉和櫻桃。

最新版本的 Excel

VLOOKUP 語法取決於您使用的 Excel 版本。

最新版本（Excel 365或Excel 2021）支持動態數組。此功能允許公式返回“溢出”到相鄰單元格中的多個結果。

這是語法（等號開始公式）：

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

• lookup_value：您要查找的值。

• table_array：要查找的整個數據表。

• col_index_num：包含要返回的數據的table_array 中的表列號。

• range_lookup：可選。指定您需要精確匹配還是近似匹配。

我們的具體示例使用此公式：

=VLOOKUP({“蘋果”，“香蕉”，“櫻桃”}，A1:A5,1,FALSE)

當在單元格 B1 中輸入公式時，結果會溢出到單元格 C1 和 D1 中。這張圖片顯示了實際的例子：

舊版本

如果您使用的是不支持動態數組的舊版 Excel（例如 Excel 2019 或更早版本），則需要對數組公式使用略有不同的方法。

按著這些次序：

1. 單擊要顯示第一項結果的單元格（例如，B 列）。

2. 在不按 Enter 鍵的情況下鍵入以下公式：=VLOOKUP({“apple”,”banana”,”cherry”}, A1:A5, 1, FALSE)

3. 按 Ctrl + Shift + Enter 將其轉換為數組公式。

4. 複製單元格 B1 並將其粘貼到下面的單元格中（或使用填充柄）。

當您使用 Ctrl + Shift + Enter 時，Excel 會在公式周圍添加大括號。這表明它是一個數組公式。

精確匹配與近似匹配

默認情況下，VLOOKUP 函數使用近似匹配。這意味著它將返回它能找到的最接近的匹配項，即使單元格值不是完全匹配項。

如果要執行精確匹配，可以將 range_lookup 參數設置為 FALSE。

請記住，近似匹配最適合有序數值。當單元格值為文本時通常不合適。

更多關於 VLOOKUP

如果您想了解有關此多功能功能的更多信息，請查看以下文章：

• 如何使用 VLOOKUP 比較 Excel 中的兩列

• 如何使用 VLOOKUP 查找差異

現在您已經設置了 VLOOKUP 函數，讓我們看一下可以以不同方式執行其操作的另外兩個函數：INDEX 和 MATCH。

如何使用 INDEX 和 MATCH 查找多個值

您可以將 INDEX 和 MATCH 函數組合在一起以在多行中查找多個值。

Excel 中的 INDEX 函數返回指定範圍內的單元格的值或引用。

=INDEX(數組，row_num，[column_num])

• 數組：要搜索值的單元格範圍。

• row_num：要從中返回值的數組中的行號。

• column_num：（可選）數組中要從中返回值的列號。如果省略，該函數返回整行。

Excel 中的 MATCH 函數返回指定範圍內值的位置。

=MATCH(lookup_value, lookup_array, [match_type])

• lookup_value：要在 lookup_array 中搜索的值。

• lookup_array：要搜索 lookup_value 的單元格範圍。

• match_type：（可選）要執行的匹配類型。如果省略，該函數將執行精確匹配。

如何在 Excel 365 中同時使用 INDEX 和 MATCH

要同時使用 INDEX 和 MATCH 在 Excel 中查找多個值，您需要使用數組公式。

使用較早的示例數據，這是 Excel 365 中的公式：

=INDEX(A1:A5, MATCH({“蘋果”,”香蕉”,”櫻桃”}, A1:A5, 0))

上面的例子分解為：

• INDEX：這會根據給定的行號和列號返回指定範圍內的單元格的值。在這種情況下，它將返回範圍 A1:A5 中的值。

• A1:A5：這是定義的表格範圍，您將在其中搜索值並從中返回結果。

• MATCH：這會在單元格範圍內搜索指定的項目，並返回該項目在該範圍內的相對位置。

• {“apple”,”banana”,”cherry”}：這是包含您要查找的值的數組常量。

• A1:A5：這是 MATCH 將從數組常量中搜索值的範圍。

• 0：這是 MATCH 函數的匹配類型。在這種情況下，它是 0，這意味著您正在尋找完全匹配而不是緊密匹配。

 這張圖片顯示了正在運行的公式：

使用舊版本的 Excel

如果您使用的是不支持動態數組的舊版 Excel 文件（例如 Excel 2019 或更早版本），則需要使用不同的方法。

由於舊版本不支持“溢出”到相鄰單元格的公式，因此您需要將用法分解為三個單獨的公式。

按著這些次序：

1. 單擊您想要第一項結果的單元格（例如，單元格 B1）

2. 輸入以下公式：

3. =INDEX(A1:A5, MATCH(“蘋果”, A1:A5, 0))

4. 按 Enter 鍵執行公式。

5. 在單元格 B2 中鍵入此公式：=INDEX(A1:A5, MATCH(“banana”, A1:A5, 0))

6. 在單元格 B3 中鍵入此公式：=INDEX(A1:A5, MATCH(“cherry”, A1:A5, 0))

此圖顯示單元格引用 B3：

INDEX 和 MATCH 函數並不是唯一可以讓您找到多個值的函數。在下一節中，我們將了解如何使用 IF 函數作為替代方法。

如何使用 IF 函數查找多個值

根據特定條件查找多個單元格值的另一種方法是將 IF 函數與其他函數一起使用。

IF 函數允許您測試多個條件並根據這些測試的結果返回不同的結果。

例如，假設您有一個包含 Product 和 Sales 列的銷售數據表。您想要查找併計算三種產品中兩種產品的總銷售額。

當前版本的 Excel

要使用 IF 函數查找產品為“Apple”或“Banana”的 Sales 列的總和，您可以使用具有 IF、SUM 和 OR 函數的數組公式。

假設您的數據從單元格 A1 開始，請使用以下公式：

=SUM(IF((A2:A4=“蘋果”)+(A2:A4=“香蕉”), B2:B4, 0))

如果區域A2:A4 中的單元格包含“Apple”或“Banana”，則 (A2:A4=”Apple”)+(A2:A4=”Banana”)部分創建一個值為 1 的數組，否則為 0 .

IF 語句檢查數組參數的每個元素。如果值為 1（即產品是“Apple”或“Banana”），則取 Sales 列中的相應值（範圍 B2:B4）；否則，取 0。

SUM 函數將 IF 函數的值相加，有效地將“Apple”和“Banana”的銷售額相加。

此圖顯示了搜索範圍內的公式：

舊版本的 Excel

在 Excel 2019 或更早版本中，您需要使用數組公式。按著這些次序：

1. 輸入公式但不要按 Enter。

2. 按 Ctrl + Shift + Enter 使其成為數組公式。

Excel 將在公式周圍添加花括號 {}，表明它是一個數組公式。

接下來，我們將了解如何使用 SUMPRODUCT 根據您的條件查找多個值。我們走吧！

如何對多個條件使用 SUMPRODUCT

SUMPRODUCT 函數還允許您根據多個條件查找多個值。

因為它不需要使用數組公式，所以無論 Excel 版本如何，語法都是相同的。

使用與上一個示例中相同的數據，公式如下所示：

=SUMPRODUCT((A2:A4=“蘋果”)+(A2:A4=“香蕉”), B2:B4)

如果區域A2:A4 中的單元格包含“Apple”或“Banana”，則 (A2:A4=”Apple”)+(A2:A4=”Banana”)部分創建一個值為 1 的數組，否則為 0 .

SUMPRODUCT 函數將數組的元素與 Sales 列（範圍 B2:B4）中的相應元素相乘。然後它會將結果值相加，有效地將“Apple”和“Banana”的銷售值相加。

下面的公式顯示了它的實際效果：

Excel 函數按預期工作時非常棒，但有時您可能會遇到錯誤。在下一節中，我們將介紹一些常見錯誤以及如何處理它們。

查找函數的 3 個常見錯誤

查找函數有時會返回錯誤，這些錯誤可能令人沮喪且耗時進行故障排除。您將遇到的三個最常見的錯誤是：

1. #N/A 錯誤

2. #參考！錯誤

3. 循環錯誤

1. #N/A 錯誤

當無法在查找數組中找到查找值時，會發生#N/A 錯誤。

出現此錯誤的原因有多種，包括：

• 查找值拼寫錯誤或不正確。

• 查找數組未按升序排序。

• 查找值不在數據集中。

當查找值不在數據集中時，這是有用的信息。然而，沒有經驗的 Excel 用戶可能會認為 #N/A 意味著公式出了問題。下一節將展示如何使它更加用戶友好。

2. #REF！錯誤

裁判！刪除或移動查找數組或返回數組時會發生錯誤。

可以通過更新查找函數中的單元格引用來修復此錯誤。

3.循環錯誤

當您在復雜的公式中組合函數時，Excel 可能會告訴您有循環引用。

通過使用我們的在 Excel 中查找循環引用的指南，您會發現這些更容易調查。

如何將 IFERROR 與查找函數一起使用

IFERROR 函數是處理查找函數中錯誤的有用工具。它允許您指定在查找函數返回錯誤時要返回的值或公式。

IFERROR 函數的語法如下：

=IFERROR（值，value_if_error）

· value：要計算的值或公式。

· Value_if_error：如果第一個參數返回錯誤，則返回值或公式。

例如，假設您有一個在表中查找多個值的 VLOOKUP 函數。在下圖中，其中一個值不存在於搜索到的數據范圍內。

如您所見，顯示了#N/A 錯誤，這可能會讓沒有經驗的 Excel 用戶感到困惑。

相反，您可以使用 IFERROR 顯示空白單元格或顯示“未找到”的消息，語法如下：

=IFERROR(VLOOKUP(lookup_value, table_array, column_index, FALSE), “未找到”)

在此示例中，如果 VLOOKUP 函數返回錯誤，IFERROR 函數將返回消息“未找到”。

這張圖片顯示了正在運行的公式。B 列有缺失值，而 C 列和 D 列找到了匹配項。

到目前為止，我們已經介紹了很多內容，您終於準備好學習更高級的查找技術，這是下一節的主題。

7 種高級查找技術

在 Excel 中查找多個值可能是一項具有挑戰性的任務，尤其是在處理大型數據集時。您可能會遇到處理速度慢的性能問題。

您可以使用七種高級查找技術來使該過程更輕鬆、更高效。

• 相對位置查找

• 小功能

• 行函數

• 過濾功能

• 輔助列

• 動態數組

• 電源查詢

1.相對位置查找

在 Excel 中查找多個值的最簡單方法之一是使用相對位置查找。這涉及指定與給定單元格的行和列偏移量以找到所需的值。

例如，如果您有一個數據表並且想要查找給定單元格下方兩行和右側三列的值，您可以使用以下公式：

=OFFSET(單元格, 2, 3)

2. 小功能

另一種用於多值查找的有用技術是使用 SMALL 函數。此函數返回單元格區域中的第 n 個最小值。

通過將此函數與其他查找函數（如 INDEX 和 MATCH）結合使用，您可以根據特定條件查找多個值。

例如，以下公式在滿足特定條件的單元格區域中查找第二小的值：

=INDEX(數據, MATCH(SMALL(IF(條件, 範圍), 2), 範圍, 0))

3.行函數

ROW 函數也可用於 Excel 中的多值查找。此函數返回給定單元格的行號，可用於引用數據表中的單元格。

例如，以下公式根據唯一標識符在表中查找值：

=INDEX（數據，匹配（unique_identifier，數據[unique_identifier]，0），column_index_number）

4.過濾功能

FILTER 函數在舊版本的 Excel 中不可用。

在 Excel 365 中，您可以使用它根據特定條件篩選一系列單元格，並僅返回滿足這些條件的值。這是語法和三個參數：

=FILTER（數組，包括，[if_empty]）

• array：要過濾的具體數據。

• include：include 參數是要應用於數組的條件或條件。

• [if_empty]（可選）：如果沒有行或列滿足 include 參數中指定的條件，則返回該值。

例如，以下公式對示例數據進行處理，以查找第一列中三項中兩項的匹配項，並在第二列中對它們的對應值求和。

=SUM(過濾器(B2:B4, (A2:A4=”蘋果”)+(A2:A4=”香蕉”)))

此圖顯示了匹配的行數以及不同值的總和：

5. 輔助列

您可以使用輔助列在 VLOOKUP 等函數中將多個字段連接在一起。

假設您在單獨的工作表中處理名字和姓氏。您可以將它們連接在最終公式中引用的輔助列中。

6. 動態數組

正如你在前面的示例中了解到的，Microsoft 365 用戶可以利用動態數組在 Excel 中進行多值查找。

動態數組允許您從單個公式返回多個值，從而更容易查找大量數據。

7. 電源查詢

Power Query 是 Excel 中的一個強大工具，可用於根據多個條件返回值。

例如，此視頻在電子表格中發現雜亂的數據並將其清理。

最後的想法

這總結了我們對在 Excel 中查找多個值的藝術的深入研究！一旦您掌握了 VLOOKUP、INDEX、MATCH 和數組公式，您會發現自己可以輕鬆處理複雜的數據集，就像熱刀切黃油一樣。

關鍵是理解每個公式背後的語法和邏輯。請記住，這些公式可能很複雜，因此在依靠它們進行重要數據分析之前，請務必花時間徹底測試您的公式。

Excel 是一個強大的工具，但它的好壞取決於用戶。因此，繼續磨練這些技能，繼續試驗，您很快就會成為多值查找的大師。直到下一次，繼續處理這些數字並讓 Excel 為您完成艱苦的工作！