Skip to main content

立即探索 Kutools for Outlook 的全新免費版本！

根據多個條件在 Excel 中返回多個匹配值（完整指南）

Author: Xiaoyang Last Modified: 2025-05-12

Excel 用戶經常遇到需要提取滿足多個條件的多個值的情況，並將所有匹配結果呈現在一列、一行或合併到單個單元格中。本指南探討了適用於所有 Excel 版本的方法，以及在 Excel 365 和 2021 中可用的新 FILTER 函數。

return multiple matching values based on criteria

根據多個條件在單個單元格中返回多個匹配值

根據多個條件在一列中返回多個匹配值

根據多個條件在一行中返回多個匹配值

根據多個條件在單個單元格中返回多個匹配值

在 Excel 中，根據多個條件在單個單元格內提取多個匹配值是一個常見的挑戰。這裡介紹兩種高效的方法。

方法 1：使用 Textjoin 函數（Excel365 / 2021, 2019）

要將所有匹配值以分隔符的形式合併到單個單元格中，TEXTJOIN 函數可以幫上忙。

在空白單元格中輸入或複製以下公式，然後按 Enter 鍵（Excel 2021 和 Excel 365），或者在 Excel 2019 中按 Ctrl + Shift + Enter 鍵來獲取結果：

=TEXTJOIN(", ", TRUE, IF(($A$2:$A$18=E2)*($B$2:$B$18=F2), $C$2:$C$18, ""))

Return multiple matching values based on multiple criteria in a single cell with textjoin function

此公式的解釋：
  • ($A$2:$A$21=E2)*($B$2:$B$21=F2) 檢查每一行是否同時符合兩個條件：“賣家等於 E2”和“月份等於 F2”。如果兩個條件都滿足，結果為 1；否則為 0。星號 * 表示兩個條件必須同時成立。
  • IF(..., $C$2:$C$21, "") 如果該行匹配，則返回產品名稱；否則返回空白。
  • TEXTJOIN(", ", TRUE, ...) 將所有非空的產品名稱合併到一個單元格中，並用 “, ” 分隔。
 

方法 2：使用 Kutools for Excel

Kutools for Excel 提供了一個強大且簡單的解決方案，讓您能夠快速檢索並根據多個條件將多個匹配項合併到單個單元格中，無需複雜的公式。

Kutools for Excel 提供超過 300 種進階功能，簡化複雜任務，提升創造力與效率。 結合 AI 能力，Kutools 能夠精準自動化任務，讓數據管理變得輕而易舉。Kutools for Excel 的詳細資訊...免費試用...

安裝 Kutools for Excel 後，請按照以下步驟操作：

  1. 選擇您希望根據條件獲取所有對應值的數據範圍。
  2. 然後，點擊 Kutools > 合併與分割 > 高級合併行，參見截圖：
    click Advanced Combine Rows of kutools
  3. 在「高級合併行」對話框中，請配置以下選項：
    • 選擇包含匹配條件的列標題（例如，賣家和月份）。對於每個選定的列，點擊主鍵將其設置為查找條件。
    • 點擊您希望合併結果的列標題（例如，產品）。從「合併」部分選擇您偏好的分隔符（例如，逗號、空格或自訂分隔符）。
  4. 最後，點擊確定按鈕。
    specify the options in the dialog box

結果：Kutools 將立即根據每組唯一的條件合併所有匹配值到單個單元格中。
Return multiple matching values based on multiple criteria in a single cell with kutools

根據多個條件在一列中返回多個匹配值

當您需要根據多個條件從數據集中提取並顯示多個匹配記錄時，並將結果以垂直列格式返回，Excel 提供了幾種強大的解決方案。

方法 1：使用陣列公式（適用於所有版本）

您可以使用以下陣列公式將結果垂直返回到一列中：

1. 在空白單元格中複製或輸入以下公式：

=IFERROR(INDEX($C$2:$C$18, SMALL(IF(($A$2:$A$18=$E$2)*($B$2:$B$18=$F$2), ROW($C$2:$C$18)-ROW($C$2)+1), ROW(1:1))), "")

2. 按 Ctrl + Shift + Enter 鍵獲取第一個匹配結果，然後選擇第一個公式單元格並向下拖動填充柄直到顯示空白單元格，此時所有匹配值均已返回，如下截圖所示：

Return multiple matching values based on multiple criteria in a column with array formula

此公式的解釋：
  • $A$2:$A$18=$E$2：檢查賣家是否與單元格 E2 的值匹配。
  • $B$2:$B$18=$F$2：檢查月份是否與單元格 F2 的值匹配。
  • * 是邏輯 AND 運算符（兩個條件都必須為真）。
  • ROW($C$2:$C$18)-ROW($C$2)+1：為每個產品生成相對行號。
  • SMALL(..., ROW(1:1))：獲取第 n 個最小的匹配行（當公式向下拖動時）。
  • INDEX(...)：返回匹配行中的產品。
  • IFERROR(..., "")：如果沒有更多匹配項，則返回空白單元格。
 

方法 2：使用 Filter 函數（Excel365 / 2021）

如果您使用的是 Excel 365 或 Excel 2021，FILTER 函數是返回基於多個條件的多個結果的絕佳選擇，這得益於其簡潔性、清晰性和動態溢出結果的能力，而無需複雜的陣列公式。

在空白單元格中複製或輸入以下公式，然後按 Enter 鍵，所有匹配記錄均根據多個條件返回。

=FILTER(C2:C18, (A2:A18=E2)*(B2:B18=F2), "No match")

Return multiple matching values based on multiple criteria in a column with filter function

此公式的解釋：
  • FILTER(...) 返回 C2:C18 中符合兩個條件的所有值。
  • (A2:A18=E2)*(B2:B18=F2)：檢查匹配賣家和月份的邏輯陣列。
  • "No match"：未找到值時的可選消息。

根據多個條件在一行中返回多個匹配值

Excel 用戶通常需要從數據集中提取符合多個條件的多個值並將它們水平顯示（在一列中）。這對於創建動態報告、儀表板或摘要表非常有用，特別是在垂直空間有限的情況下。在本節中，我們將探討兩種強大的方法。

方法 1：使用陣列公式（適用於所有版本）

傳統的陣列公式允許使用 INDEX、SMALL、IF 和 COLUMN 函數提取多個匹配值。與垂直提取（基於列）不同，我們調整公式以在一行中返回結果。

1. 在空白單元格中複製或輸入以下公式：

=IFERROR(INDEX($C$2:$C$18, SMALL(IF(($A$2:$A$18=$E$2)*($B$2:$B$18=$F$2), ROW($C$2:$C$18)-ROW($C$2)+1), COLUMN(A1))), "")

2. 按 Ctrl + Shift + Enter 鍵獲取第一個匹配結果，然後選擇第一個公式單元格並向右拖動公式跨越列以獲取所有結果。

Return multiple matching values based on multiple criteria in a row with array formula

此公式的解釋：
  • $A$2:$A$18=$E$2：檢查賣家是否匹配。
  • $B$2:$B$18=$F$2：檢查月份是否匹配。
  • *：邏輯 AND——兩個條件都必須為真。
  • ROW($C$2:$C$18)-ROW($C$2)+1：創建相對行號。
  • COLUMN(A1)：根據公式向右拖動的程度調整返回哪個匹配項。
  • IFERROR(...)：防止匹配耗盡後出現錯誤。
 

方法 2：使用 Filter 函數（Excel365 / 2021）

在空白單元格中複製或輸入以下公式，然後按 Enter 鍵，所有匹配值均被提取並定位在一行中。參見截圖：

=TRANSPOSE(FILTER(C2:C18, (A2:A18=E2)*(B2:B18=F2), "No match"))

Return multiple matching values based on multiple criteria in a row with filter function

此公式的解釋：
  • FILTER(...)：根據兩個條件從列 C 中檢索匹配值。
  • (A2:A18=E2)*(B2:B18=F2)：兩個條件都必須為真。
  • TRANSPOSE(...)：將 FILTER 返回的垂直陣列轉換為水平陣列。

? 結論

根據多個條件在 Excel 中檢索多個匹配值可以通過多種方式實現，具體取決於您希望如何顯示結果——無論是在列、行還是單個單元格中。

  • 對於使用 Excel 365 或 Excel 2021 的用戶，FILTER 函數提供了一個現代化、動態且優雅的解決方案，最大限度地減少了複雜性。
  • 對於使用舊版本的用戶，陣列公式仍然是強大的工具，儘管它們需要更多的設置和注意。
  • 此外，如果您想將結果合併到單個單元格中或偏好無代碼解決方案，TEXTJOIN 函數或像 Kutools for Excel 這樣的第三方工具可以顯著簡化流程。

選擇最適合您的 Excel 版本和首選佈局的方法，您將能夠高效準確地處理多條件查找。如果您有興趣探索更多 Excel 技巧和竅門，我們的網站提供了數千個教程，幫助您掌握 Excel。

更多相關文章：

  • 在一個逗號分隔的單元格中返回多個查找值
  • 在 Excel 中，我們可以應用 VLOOKUP 函數從表格單元格中返回第一個匹配值，但有時我們需要提取所有匹配值並將其以特定分隔符（如逗號、破折號等）分隔到單個單元格中，如下截圖所示。如何在 Excel 中獲取並返回多個查找值到一個逗號分隔的單元格中？
  • 在 Google 表格中一次性返回多個匹配值的 Vlookup
  • Google 表格中的普通 Vlookup 函數可以幫助您根據給定數據查找並返回第一個匹配值。但是，有時您可能需要 vlookup 並返回所有匹配值，如下截圖所示。您是否有任何好用且簡單的方法來解決這個問題？
  • Vlookup 並從下拉列表中返回多個值
  • 在 Excel 中，如何通過下拉列表進行 vlookup 並返回多個對應值，這意味著當您從下拉列表中選擇一個項目時，所有相關值都會立即顯示，如下截圖所示。本文將逐步介紹解決方案。
  • 在 Excel 中垂直返回多個匹配值的 Vlookup
  • 通常，您可以使用 Vlookup 函數獲取第一個對應值，但有時您希望根據特定條件返回所有匹配記錄。本文將討論如何 vlookup 並垂直、水平或合併到單個單元格中返回所有匹配值。

最佳辦公效率工具

🤖 Kutools AI 助手：基於智能執行方式革新數據分析：智能執行   |  生成代碼  |  創建自訂公式  |  分析數據並生成圖表  |  調用 Kutools 函數…
熱門功能：查找、標記重複值或識別重複項   |  刪除空行   |  合併列或單元格而不丟失數據   |   四捨五入無需公式 ...
高級 LOOKUP： 多條件 VLookup  |   多值 VLookup  |   多表查找   |   模糊查找 ....
高級下拉列表：快速創建下拉列表   |  依賴下拉列表   |  多選下拉列表 ....
列管理器： 添加特定數量的列  |  移動列  |  切換隱藏列的可見狀態  |  比較區域和列 ...
特色功能：網格聚焦   |  設計檢視   |   增強編輯欄   |  工作簿與工作表管理器   |  資源庫（自動文本）   |  日期提取器   |  合併資料   |  加密/解密儲存格   |  按列表發送電子郵件   |  超級篩選   |   特殊篩選（篩選粗體/斜體/刪除線...） ...
頂級 15 種工具集:  12 個文本工具（添加文本、刪除特定字符、...）   |   50+ 圖表 類型（甘特圖、...）   |   40+ 實用 公式（基於生日計算年齡、...）   |   19 個插入工具（插入QR碼、根據路徑插入圖片、...）   |   12 個轉換工具（金額轉大寫、匯率轉換、...）   |   7 個合併與分割工具（高級合併行、分割儲存格、...）   |   ... 還有更多

使用 Kutools for Excel 提升您的 Excel 技巧，體驗前所未有的高效。 Kutools for Excel 提供超過 300 種高級功能來提高生產力並節省時間。  點擊這裡獲取您最需要的功能...

Office Tab 將標籤式界面帶到 Office，讓您的工作更加輕鬆

  • 在 Word、Excel、PowerPoint、Publisher、Access、Visio 和 Project 中啟用標籤式編輯和閱讀。
  • 在同一窗口的新標籤中打開和創建多個文檔，而不是在新窗口中。
  • 將您的生產力提高 50%，每天為您減少數百次鼠標點擊！