跳到主要內容

根據一個或多個條件查找並返回多個值

通常，您可以使用Vlookup函數來獲取第一個對應的值，但是有時，您希望基於特定條件返回所有匹配的記錄。 在本文中，我將討論如何進行vlookup並將所有匹配值垂直，水平或返回到單個單元格中。

Vlookup並垂直返回所有對應的值

Vlookup並水平返回所有對應的值

Vlookup並將所有對應的值返回到一個單元格中

Vlookup並垂直返回所有對應的值

要根據特定條件垂直返回所有匹配值，請應用以下數組公式：

1。 輸入此公式或將其複製到要輸出結果的空白單元格中：

=IFERROR(INDEX($C$2:$C$20, SMALL(IF($E$2=$A$2:$A$20, ROW($A$2:$A$20)-ROW($A$2)+1), ROW(1:1))),"" )

備註：在以上公式中， C2：C20 該列是否包含您要返回的匹配記錄； A2：A20 該列是否包含條件； 和 E2 是您要基於其返回值的特定條件。 請根據需要更改它們。

2。 然後按 Ctrl + Shift + Enter 鍵一起獲得第一個值，然後向下拖動填充手柄以獲取所需的所有相應記錄，請參見屏幕截圖：

提示：

要根據更具體的值垂直查找並返回所有匹配值，請應用以下公式，然後按 Ctrl + Shift + Enter 鍵。

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

Vlookup並水平返回所有對應的值

如果要獲取水平顯示的匹配值，下面的數組公式可以為您提供幫助。

1。 輸入此公式或將其複製到要輸出結果的空白單元格中：

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

備註：在以上公式中， C2：C20 該列是否包含您要返回的匹配記錄； A2：A20 該列是否包含條件； 和 F1 是您要基於其返回值的特定條件。 請根據需要更改它們。

2。 然後按 Ctrl + Shift + Enter 鍵一起獲得第一個值，然後向右拖動填充手柄以獲取所需的所有相應記錄，請參見屏幕截圖：

提示：

要根據更具體的值橫向查找並返回所有匹配值，請應用以下公式，然後按 Ctrl + Shift + Enter 鍵。

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

Vlookup並將所有對應的值返回到一個單元格中

要將vlookup並將所有對應的值返回到單個單元格中，應應用以下數組公式。

1。 在公式下方輸入或複製到空白單元格中：

=TEXTJOIN(", ",TRUE,IF($A$2:$A$20=F1,$C$2:$C$20,""))

備註：在以上公式中， C2：C20 該列是否包含您要返回的匹配記錄； A2：A20 該列是否包含條件； 和 F1 是您要基於其返回值的特定條件。 請根據需要更改它們。

2。 然後按 Ctrl + Shift + Enter 鍵一起將所有匹配的值合併到一個單元格中，請參見屏幕截圖：

提示：

要根據單個單元格中更具體的值查找並返回所有匹配值，請應用以下公式，然後按 Ctrl + Shift + Enter 鍵。

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

注意： 此公式僅在Excel 2016及更高版本中成功應用。 如果您沒有Excel 2016，請查看 這裡 把它弄下來。

更多有關Vlookup的文章：

  • Vlookup並從下拉列表中返回多個值
  • 在Excel中，如何從下拉列表中進行vlookup並返回多個相應的值，這意味著當您從下拉列表中選擇一項時，其所有相對值會立即顯示，如以下屏幕截圖所示。 本文，我將逐步介紹解決方案。
  • Vlookup在Excel中返回空白而不是0或N / A
  • 通常，當您應用vlookup函數返回相應的值時，如果匹配的單元格為空，則它將返回0，如果找不到匹配的值，則會收到錯誤＃N / A值。 而不是顯示0或＃N / A值，如何使其顯示空白單元格？
  • Vlookup從Excel表返回多列
  • 在Excel工作表中，您可以應用Vlookup函數從一列中返回匹配值。 但是，有時，您可能需要從多個列中提取匹配的值，如下圖所示。 如何使用Vlookup函數從多個列中同時獲取相應的值？
  • 跨多個工作表的Vlookup值
  • 在excel中，我們可以輕鬆地應用vlookup函數在工作表的單個表中返回匹配值。 但是，您是否曾經考慮過如何在多個工作表中實現vlookup價值？ 假設我有以下三個具有數據范圍的工作表，現在，我想根據這三個工作表中的條件來獲取相應值的一部分。
  • 超級公式欄 （輕鬆編輯多行文本和公式）； 閱讀版式 （輕鬆讀取和編輯大量單元格）； 粘貼到過濾範圍...
  • 合併單元格/行/列 和保存數據； 拆分單元格內容； 合併重複的行和總和/平均值...防止細胞重複； 比較範圍...
  • 選擇重複或唯一 行; 選擇空白行 （所有單元格都是空的）； 超級查找和模糊查找 在許多工作簿中； 隨機選擇...
  • 確切的副本 多個單元格，無需更改公式參考； 自動創建參考 到多張紙； 插入項目符號，複選框等...
  • 收藏并快速插入公式，範圍，圖表和圖片； 加密單元 帶密碼 創建郵件列表 並發送電子郵件...
  • 提取文字，添加文本，按位置刪除， 刪除空間; 創建和打印分頁小計； 在單元格內容和註釋之間轉換...
  • 超級濾鏡 （將過濾方案保存並應用於其他工作表）； 高級排序 按月/週/日，頻率及更多； 特殊過濾器 用粗體，斜體...
  • 結合工作簿和工作表; 根據關鍵列合併表； 將數據分割成多個工作表; 批量轉換xls，xlsx和PDF...
  • 數據透視表分組依據 週號，週幾等 顯示未鎖定的單元格 用不同的顏色 突出顯示具有公式/名稱的單元格...
kte選項卡201905
  • 在Word，Excel，PowerPoint中啟用選項卡式編輯和閱讀，發布者，Access，Visio和Project。
  • 在同一窗口的新選項卡中而不是在新窗口中打開並創建多個文檔。
  • 將您的工作效率提高 50%，每天為您減少數百次鼠標點擊！
officetab底部
Comments (8)
No ratings yet. Be the first to rate!
Ahmed Abosamaha
Ahmed Abosamaha
This comment was minimized by the moderator on the site
gents

would you please share with me the formula of the three ways but for data in horizontal table.
Reply
0
0
skyyang
skyyang     Ahmed Abosamaha
This comment was minimized by the moderator on the site
Hello, Ahmed,
To solve your problem, please apply the below formulas:
Get the results vertically: =IFERROR(INDEX($B$2:$K$2, SMALL(IF($A$5=$B$1:$K$1, COLUMN($B$1:$K$1:$K$1)-COLUMN($B$1)+1), ROW(1:1))),"" )
Get the results horizontally: =IFERROR(INDEX($B$2:$K$2, SMALL(IF($D$5=$B$1:$K$1, COLUMN($B$1:$K$1:$K$1)-COLUMN($B$1)+1), COLUMN(A1))),"" )
Ge the results in one cell: =TEXTJOIN(", ",TRUE,IF($B$1:$K$1=J5,$B$2:$K$2,""))
Note: These formulas are array formulas, you should press Ctrl + Shift + Enter keys together to get the correct result.

https://www.extendoffice.com/images/stories/comments/comment-skyyang/doc-vlookup-data.png

Pease try, hope this can help you!
Reply
Report
0
0
Ahmed Abosamaha
Ahmed Abosamaha     skyyang
This comment was minimized by the moderator on the site
thanks for your greet support and quick response
Reply
0
0
itika
itika
This comment was minimized by the moderator on the site
how do u get all rows to fill up like in yr video without pressing ctrl+shift+enter
Reply
0
0
Taia
Taia
This comment was minimized by the moderator on the site
After I extend the data, some of the cells is showing a "0" instead of blank
Reply
0
0
Adam
Adam     Taia
This comment was minimized by the moderator on the site
It means that there is missing data or the wrong type of data in the cell(s) from the column that is being referrenced. For instance, Im working with a bunch of data and I want all ID numbers for workers under a certain supervisor. However, in the table column being referenced with all workers' ID numbers, some cells had the workers last name in it and not their ID numbers. Those exact cells produced 0s when the entire formula was put in, and this is because it was text within a column that is mostly numbers.
Reply
0
0
Win E
Win E
This comment was minimized by the moderator on the site
How can you tweak the formula so that you have multiple outputs in column D? E.g. I want to populate column D with each of the countries, such that all of their respective cities will be returned in E? The only work around I have can see is manually changing $D$2
Reply
0
0
Jeff
Jeff
This comment was minimized by the moderator on the site
Thank you very much, you save lots of work here!





Regarding your formula {=TEXTJOIN(", ",TRUE,IF($A$2:$A$15=D2,$B$2:$B$15,"")},

I changed a little : (=TEXTJOIN(CHAR(10),TRUE,IF($A$2:$A$15=D2,$B$2:$B$15,""))}





Regards,



Jeff
Reply
0
0
There are no comments posted here yet
Please leave your comments in English
Guest
Posting as Guest
×
Rate this post:
Reset
0   Characters
Suggested Locations