Note: The other languages of the website are Google-translated. Back to English

如何在Excel中基於一個或多個條件返回多個匹配值?

通常,對於大多數人來說,使用VLOOKUP函數查找特定值並返回匹配項很容易。 但是,您是否曾經嘗試過根據一個或多個條件返回多個匹配值,如下面的屏幕截圖所示? 在本文中,我將介紹一些解決Excel中復雜任務的公式。

根據一個或多個條件使用數組公式返回多個匹配值


根據一個或多個條件使用數組公式返回多個匹配值

例如,我要提取年齡為28歲且來自美國的所有姓名,請使用以下公式:

1。 將以下公式複製或輸入到要查找結果的空白單元格中:

=INDEX($B$2:$B$11, SMALL(IF(COUNTIF($F$2, $C$2:$C$11)*COUNTIF($G$2, $D$2:$D$11), ROW($A$2:$D$11)-MIN(ROW($A$2:$D$11))+1), ROW(A1)), COLUMN(A1))

備註:在以上公式中, B2:B11 是從中返回匹配值的列; F2, C2:C11 是第一個條件和包含第一個條件的列數據; G2, D2:D11 是第二個條件,包含此條件的列數據,請根據需要進行更改。

2。 然後按 Ctrl + Shift + Enter 鍵以獲取第一個匹配結果,然後選擇第一個公式單元格並將填充手柄向下拖動到這些單元格,直到顯示錯誤值,現在,所有匹配值都將返回,如下圖所示:

保養竅門:如果只需要根據一種條件返回所有匹配值,請應用以下數組公式:

=IFERROR(INDEX($B$2:$B$11, SMALL(IF($F$2=$D$2:$D$11, ROW($D$2:$D$11)-ROW($D$2)+1), ROW(1:1))),"" )


更多相關文章:

  • 在一個逗號分隔的單元格中返回多個查找值
  • 在Excel中,我們可以應用VLOOKUP函數從表單元格返回第一個匹配的值,但是有時,我們需要提取所有匹配的值,然後用特定的定界符(例如逗號,破折號等)分隔為單個單元格如下圖所示。 我們如何在Excel中的一個逗號分隔的單元格中獲取並返回多個查找值?
  • Vlookup並在Google表格中一次返回多個匹配值
  • Google表格中的常規Vlookup函數可以幫助您根據給定的數據查找並返回第一個匹配值。 但是,有時,您可能需要vlookup並返回所有匹配的值,如下圖所示。 您是否有任何簡便的方法可以解決Google表格中的此任務?
  • Vlookup並從下拉列表中返回多個值
  • 在Excel中,如何從下拉列表中進行vlookup並返回多個相應的值,這意味著當您從下拉列表中選擇一項時,其所有相對值會立即顯示,如以下屏幕截圖所示。 本文,我將逐步介紹解決方案。
  • Vlookup並在Excel中垂直返回多個值
  • 通常,您可以使用Vlookup函數來獲取第一個對應的值,但是有時,您希望基於特定條件返回所有匹配的記錄。 在本文中,我將討論如何進行vlookup並將所有匹配值垂直,水平或返回到單個單元格中。
  • Vlookup並在Excel中返回兩個值之間的匹配數據
  • 在Excel中,我們可以應用常規的Vlookup函數根據給定的數據獲取相應的值。 但是,有時,我們想要vlookup並返回兩個值之間的匹配值,如下面的屏幕截圖所示,您如何在Excel中處理此任務?

 


  • 超級公式欄 (輕鬆編輯多行文本和公式); 閱讀版式 (輕鬆讀取和編輯大量單元格); 粘貼到過濾範圍...
  • 合併單元格/行/列 和保存數據; 拆分單元格內容; 合併重複的行和總和/平均值...防止細胞重複; 比較範圍...
  • 選擇重複或唯一 行; 選擇空白行 (所有單元格都是空的); 超級查找和模糊查找 在許多工作簿中; 隨機選擇...
  • 確切的副本 多個單元格,無需更改公式參考; 自動創建參考 到多張紙; 插入項目符號,複選框等...
  • 收藏并快速插入公式,範圍,圖表和圖片; 加密單元 帶密碼 創建郵件列表 並發送電子郵件...
  • 提取文字,添加文本,按位置刪除, 刪除空間; 創建和打印分頁小計; 在單元格內容和註釋之間轉換...
  • 超級濾鏡 (將過濾方案保存並應用於其他工作表); 高級排序 按月/週/日,頻率及更多; 特殊過濾器 用粗體,斜體...
  • 結合工作簿和工作表; 根據關鍵列合併表; 將數據分割成多個工作表; 批量轉換xls,xlsx和PDF...
  • 數據透視表分組依據 週號,週幾等 顯示未鎖定的單元格 用不同的顏色 突出顯示具有公式/名稱的單元格...
kte選項卡201905
  • 在Word,Excel,PowerPoint中啟用選項卡式編輯和閱讀,發布者,Access,Visio和Project。
  • 在同一窗口的新選項卡中而不是在新窗口中打開並創建多個文檔。
  • 將您的工作效率提高 50%,每天為您減少數百次鼠標點擊!
officetab底部

 

按評論排序
留言 (25)
還沒有評分。 成為第一位評論!
網站主持人對此評論進行了最小化
我嘗試了這個完全相同的公式; 100% 複製。 我唯一改變的是匹配和返回的數據。 當我使用此公式時,Excel 顯示“您為此函數輸入了太多參數)。=INDEX('2020 Volume Report'!$B$3:$B$100,SMALL(IF(COUNTIF($A$1,'2020 Volume報告'!$A$3:$A$100)*COUNTIF($A$3,'2020年成交量報告'!$D$3:$D$100),ROW('2020年成交量報告'!$A$3:$G$100)- MIN(ROW('2020 年成交量報告'!$A$3:$G$100))+1,"0"),ROW(A1),COLUMN(A1))
網站主持人對此評論進行了最小化
嗨,你能把你的數據和公式錯誤作為一個屏幕截圖在這裡嗎?
網站主持人對此評論進行了最小化
您好,我如何將它用於水平條件。
網站主持人對此評論進行了最小化
公式中+0後面的“1”是什麼? 這不在示例一中。
網站主持人對此評論進行了最小化
嗨,我嘗試過相同的公式。 我得到了結果,但是當給 CSE 時它沒有提供任何多重響應
網站主持人對此評論進行了最小化

網站主持人對此評論進行了最小化
關於使用數組公式返回基於一個或多個條件的多個匹配值:為什麼如果我在除 A1 之外的其他任何地方都有數據,即使我更新了公式中的所有單元格引用,它也不起作用?
網站主持人對此評論進行了最小化
在第一個示例中,需要對公式進行哪些更改才能返回小於 28 歲的每個人?
網站主持人對此評論進行了最小化
嗨,

我想知道是否可以輸入第二個標準,但與第一個標準的範圍相同,

例如,對於上面使用的示例,我想搜索來自美國和法國的人的姓名所以單元格 F3 將包含法國,斯嘉麗和安德魯也將填充在 G 列的列表中

提前感謝您的幫助。
網站主持人對此評論進行了最小化
你好尼克

樂意效勞。 如果你想同時得到美國和法國人的名字,我建議你使用我們的公式兩次來得到結果。 請看截圖,在 F2 和 G2 中是值“美國”和“法國”。 應用公式 =IFERROR(INDEX($B$2:$B$11, SMALL(IF($F$2=$D$2:$D$11, ROW($D$2:$D$11)-ROW($D$2)+1 ), ROW(1:1))),"" ) 以獲得美國的結果。 並應用公式 =IFERROR(INDEX($B$2:$B$11, SMALL(IF($G$2=$D$2:$D$11, ROW($D$2:$D$11)-ROW($D$2)+ 1), ROW(1:1))),"" ) 得到法國的結果。 這很簡單。 請試一試。

此致,
曼迪
查看附件 (1 / 5)
網站主持人對此評論進行了最小化
當我使用第二個公式並向下拖動時,什麼也沒有出現。 公式結果 (fx) 說它應該返回一些東西,但它是空白的。 我該如何糾正?
網站主持人對此評論進行了最小化
你好阿莉西亞,

樂意效勞。 我嘗試了文章中的第二個公式並將公式向下拖動,其餘結果都返回了。 我認為您的問題可能有兩個原因。 首先,您可能忘記按 Ctrl + Shift + Enter 鍵輸入公式。 其次,匹配結果只有一個,所以沒有其他結果不返回。 請檢查一下。

此致,
曼迪
網站主持人對此評論進行了最小化
您好,
我嘗試使用該公式,它生成的值為 0 或附加的圖像
查看附件 (1 / 5)
網站主持人對此評論進行了最小化
你好,米爾庫
你的截圖顯示的是MAC版的WPS軟件,所以我不確定我們的公式是否可用。
我上傳了一個 Excel 文件到這裡,您可以嘗試看看它是否可以在您的環境中正確計算。
感謝您!
查看附件 (1 / 5)
網站主持人對此評論進行了最小化
你好,
在以下情況下擴展第一個公式需要什麼:
一些 ID 為空白(例如單元格 A5 為空白),我想要一個附加條件,僅當 ID 不為空白時才輸出行。 (所以輸出應該是詹姆斯和阿卜杜勒。
謝謝!
網站主持人對此評論進行了最小化
你好,喬,
為了解決您的問題,請應用以下公式:
=INDEX($B$2:$B$11, SMALL(IF(COUNTIF($F$2, $C$2:$C$11)*COUNTIF($G$2, $D$2:$D$11)*($A$2:$A$11<>0), ROW($A$2:$D$11)-MIN(ROW($A$2:$D$11))+1), ROW(A1)), COLUMN(A1))

請試一試,希望對你有幫助!
網站主持人對此評論進行了最小化
嗨,

如果在單元格 H1 中我寫“名稱”並想將其與公式鏈接,那將如何工作?
然後我可以在單元格 H1 中寫“ID”,結果會自動得到:AA1004; DD1009; PP1023(用於第一個公式)

預先感謝您!
網站主持人對此評論進行了最小化
你好,瑪麗
抱歉,我無法理解您的第一個問題,您能否更清楚詳細地解釋您的問題? 或者您可以在此處插入屏幕截圖來描述您的問題。
至於第二個問題,您只需將單元格引用更改為:
=INDEX($A$2:$A$11, SMALL(IF(COUNTIF($F$2, $C$2:$C$11)*COUNTIF($G$2, $D$2:$D$11), ROW($A$2:$D$11)-MIN(ROW($A$2:$D$11))+1), ROW(A1)), COLUMN(A1))

記得按 Ctrl + Shift + Enter 鑰匙在一起。
請嘗試,希望對您有所幫助!
網站主持人對此評論進行了最小化
嘿嘿,謝謝你的公式。 它適用於“固定”值/文本作為標準。 但是,我嘗試使用的標準之一是條件(值 <>0 ),但不適用於所描述的公式。 你們知道我應該改變什麼來適應公式,所以我可以有一個條件作為標準之一,好嗎?

最好的,

若昂
網站主持人對此評論進行了最小化
你好,馬庫斯
要解決您的問題,請查看這篇文章:
https://www.extendoffice.com/documents/excel/6393-excel-vlookup-function.html#b3-2
這個任務有一些詳細的解釋。 您只需要將標準更改為您自己的。
感謝您!
網站主持人對此評論進行了最小化
嗨,

首先,謝謝分享!

您能否為以下案例提供解決方案:

我有 3 列(A:包含參考信息,B:包含要搜索的信息,C:搜索結果)

圖片網址如下

https://ibb.co/VHCd09K

A列-------------B列------------C列
文件名-------------------------名稱----文件名,文件名,元素名稱、名稱
已更改元素------------------元素-------------已更改元素、元素名稱、元素 ID
列位置
文檔名稱
元素名稱
姓名
類別
商品保修條款

元素 ID

我需要在 A 列中搜索與單元格 B2(名稱)或 B3(元素)的任何部分匹配,並在一個單元格中獲取結果,

謝謝你,貝扎德
網站主持人對此評論進行了最小化
你好,貝扎德
也許下面的用戶定義函數可以幫助你。
Public Function ConcatPartLookUp(rngInput As Range, rngSource As Range, Optional strDelimiter As String, Optional blCaseSensitive)
Dim rng As Range
If strDelimiter = "" Then strDelimiter = ","
If IsMissing(blCaseSensitive) Then
    blCaseSensitive = False
Else
    blCaseSensitive = True
End If
For Each rng In rngSource
    If blCaseSensitive Then
        If InStr(1, rng.Value, rngInput.Value, vbBinaryCompare) > 0 Then ConcatPartLookUp = ConcatPartLookUp & strDelimiter & rng.Value
    Else
        If InStr(1, rng.Value, rngInput.Value, vbTextCompare) > 0 Then ConcatPartLookUp = ConcatPartLookUp & strDelimiter & rng.Value
    End If
Next
If Len(ConcatPartLookUp) > 0 Then ConcatPartLookUp = Mid(ConcatPartLookUp, 2, Len(ConcatPartLookUp))
End Function


複製並粘貼此代碼後,然後使用此公式:=ConcatPartLookUp(B2,$A$2:$A$8) 得到你需要的結果。
請試一試,希望對您有所幫助!
網站主持人對此評論進行了最小化
嗨,

感謝您發布這些示例。
我正在嘗試在我自己的工作表中實現它,但沒有讓它工作(可能是因為我使用的是歐洲版本的 excel)?

我想獲取我輪班或為客戶工作“一些”(>0)小時的日期。

所以在 I3 中是名稱,在 J3 中是月份。 K3 和 L3 是班次(1 工作)和小時數(不知道如何設置,應該大於零)

我的預期結果是:
班次:I7 和 I8
小時:J7

所以我在 0 年 2 月 3 日為“第 10 個人”工作了 2022 多個小時
第 2 個人在 '10-10-2022' 和 28-10-2022 輪班

當我添加 '=INDEX($B$2:$B$11, SMALL(IF(COUNTIF($F$2, $C$2:$C$11)*COUNTIF($G$2, $D$2:$D$11), ROW ($A$2:$D$11)-MIN(ROW($A$2:$D$11))+1), ROW(A1)), COLUMN(A1))' 在我的 Excel 表中,它不允許公式不同部分之間的逗號。
所以我需要將它們更改為';'。
但是當我嘗試它時,它總是說:'#NAME?

那麼有人可以幫我解決這個問題嗎?

親切的問候,

巴斯
[img]https://drive.google.com/file/d/1iIPQKuj_PNhqWyWlwJ4IQTqGNEd6B9Hw/view?usp=share_link[/img]
網站主持人對此評論進行了最小化
嗨,如果有重複的值(例如兩個亞當),我如何確保它只返回 1 個亞當而不是 2 個?
網站主持人對此評論進行了最小化
你好,鮑比,
要僅提取唯一的匹配值,您應該應用以下公式:
粘貼公式後,請按 Ctrl + Shift + Enter 鍵在一起以獲得正確的結果。
=IFERROR(INDEX($B$2:$B$5, MATCH(0, COUNTIF(H1:$H$1, $B$2:$B$5)+IF($D$2:$D$5<>$G$2, 1 , 0)+IF($C$2:$C$5<>$F$2, 1, 0), 0)), "")

請試一試,希望對您有所幫助!
這裡還沒有評論
留下你的意見
以訪客身份發帖
×
評價此帖子:
0   字符
推薦地點

關注我們

版權所有©2009 - 萬維網。extendoffice.com。 | 版權所有。 供電 ExtendOffice。 |
Microsoft和Office徽標是Microsoft Corporation在美國和/或其他國家的商標或註冊商標。
受Sectigo SSL保護