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

如何在Excel中vlookup並連接多個對應值?

眾所周知, VLOOKUP Excel中的函數可以幫助我們查找一個值並在另一列中返回相應的數據,但是通常,如果存在多個匹配數據,則它只能獲取第一個相對值。 在本文中,我將討論如何僅在一個單元格或垂直列表中進行vlookup並串聯多個對應的值。

Vlookup並使用公式垂直返回多個匹配值

Vlookup並使用用戶定義的函數連接單元格中的多個匹配值

Vlookup並使用Kutools for Excel連接單元格中的多個匹配值


假設,我有以下數據范圍,要根據特定的值垂直獲取所有相應的值(如以下屏幕截圖所示),可以應用數組公式。

doc vlookup串聯1

1。 輸入以下公式: =IF(COUNTIF($A$1:$A$16,$D$2)>=ROWS($1:1),INDEX($B$1:$B$16,SMALL(IF($A$1:$A$16=$D$2,ROW($1:$16)),ROW(1:1))),"") 放入要放入結果的空白單元格,例如E2,然後按 Ctrl + Shift + Enter 鍵一起獲得基於特定條件的相對值,請參見屏幕截圖:

doc vlookup串聯2

備註:在以上公式中:

A1:A16 是包含要查找的特定值的列範圍;

D2 指示您要vlookup的特定值;

B1:B16 是您要從中返回相應數據的列範圍;

$ 1:$ 16 指示範圍內的行引用。

2。 然後選擇單元格E2,並將填充手柄向下拖動到這些單元格,直到獲得空白單元格,所有匹配的值都列在該列中,如以下屏幕截圖所示:

doc vlookup串聯3


有時,您希望將匹配的值合併到一個單元格中,並使用特定的分隔符將它們串聯起來,而不是垂直獲取相對值。 在這種情況下,以下用戶定義功能可能會有所幫助。

1. 按住 ALT + F11 鍵打開 Microsoft Visual Basic for Applications 窗口。

2。 點擊 插入 > 模塊,然後將以下代碼粘貼到 模塊 窗口。

VBA代碼:Vlookup並連接單元格中的多個匹配值

Function CusVlookup(lookupval, lookuprange As Range, indexcol As Long)
'updateby Extendoffice
Dim x As Range
Dim result As String
result = ""
For Each x In lookuprange
    If x = lookupval Then
        result = result & " " & x.Offset(0, indexcol - 1)
    End If
Next x
CusVlookup = result
End Function

3。 然後保存並關閉此代碼,返回到工作表,然後輸入以下公式: = cusvlookup(D2,A1:B16,2) 放入要放入結果的空白單元格,然後按 Enter 鍵,基於特定數據的所有相應值都已返回到一個帶有空格分隔符的單元格中,請參見屏幕截圖:

doc vlookup串聯4

備註:在以上公式中: D2 表示要查找的單元格值, A1:B16 是您要獲取數據的數據范圍,數字 2 是要從中返回匹配值的列號,您可以根據需要更改這些引用。


如果你有 Excel的Kutools,其 高級合併行 功能,您可以輕鬆輕鬆地完成這項工作。 此功能可以幫助您基於另一列中的相同數據,將所有匹配值與特定定界符組合在一起。

Excel的Kutools : 帶有300多個便捷的Excel加載項,可以在30天內免費試用.

安裝後 Excel的Kutools,請執行以下操作:

1. 根據特定數據選擇要獲取相應值的數據范圍。

2。 然後點擊 庫工具 > 合併與拆分 > 高級合併行,請參見屏幕截圖:

3。 在 高級合併行 對話框中,單擊要合併的列名,然後單擊 首要的關鍵 按鈕,請參見屏幕截圖:

doc vlookup串聯6

4。 然後單擊要返回匹配值的另一個列名,然後單擊“確定”。 結合 選擇一個分隔符以分隔組合值,請參見屏幕截圖:

doc vlookup串聯7

5。 然後點擊 Ok 按鈕,所有基於相同值的對應值已與特定的分隔符組合在一起,請參見屏幕截圖:

doc vlookup串聯8 2 doc vlookup串聯9

 立即下載和免費試用Excel的Kutools!


Excel的Kutools:具有300多個方便的Excel加載項,可以在30天內免費試用,沒有任何限制。 立即下載並免費試用!

最佳辦公效率工具

Kutools for Excel 解決了你的大部分問題,並將你的生產力提高了 80%

  • 重用: 快速插入 複雜的公式,圖表 以及您以前使用過的任何東西; 加密單元 帶密碼 創建郵件列表 並發送電子郵件...
  • 超級公式欄 (輕鬆編輯多行文本和公式); 閱讀版式 (輕鬆讀取和編輯大量單元格); 粘貼到過濾範圍...
  • 合併單元格/行/列 不會丟失數據; 拆分單元格內容; 合併重複的行/列...防止細胞重複; 比較範圍...
  • 選擇重複或唯一 行; 選擇空白行 (所有單元格都是空的); 超級查找和模糊查找 在許多工作簿中; 隨機選擇...
  • 確切的副本 多個單元格,無需更改公式參考; 自動創建參考 到多張紙; 插入項目符號,複選框等...
  • 提取文字,添加文本,按位置刪除, 刪除空間; 創建和打印分頁小計; 在單元格內容和註釋之間轉換...
  • 超級濾鏡 (將過濾方案保存並應用於其他工作表); 高級排序 按月/週/日,頻率及更多; 特殊過濾器 用粗體,斜體...
  • 結合工作簿和工作表; 根據關鍵列合併表; 將數據分割成多個工作表; 批量轉換xls,xlsx和PDF...
  • 超過 300 項強大的功能. 支持 Office / Excel 2007-2021 和 365。支持所有語言。 在您的企業或組織中輕鬆部署。 完整功能 30 天免費試用。 60 天退款保證。
kte選項卡201905

Office選項卡為Office帶來了選項卡式界面,使您的工作更加輕鬆

  • 在Word,Excel,PowerPoint中啟用選項卡式編輯和閱讀,發布者,Access,Visio和Project。
  • 在同一窗口的新選項卡中而不是在新窗口中打開並創建多個文檔。
  • 將您的工作效率提高 50%,每天為您減少數百次鼠標點擊!
officetab底部
按評論排序
留言 (16)
還沒有評分。 成為第一位評論!
網站主持人對此評論進行了最小化
如何得到結果。 請幫忙。 數據 data1 結果 a 1 a1 b 2 a2 c b1 b2 c1 c2
網站主持人對此評論進行了最小化
使用 cusvlookup 時,是否可以添加姓氏以及中間可能出現在 C 列中的逗號
網站主持人對此評論進行了最小化
喜歡 Excel 2013 的功能,但稍作修改,將分隔字符更改為“;” 而不是“”,然後刪除前綴“;” 在我的示例中,結果匹配值將具有 ;result01 或 ;result01;result02 。 添加了額外的 If Left(xResult, 1) = ";" 刪除任何多餘的“;” 如果它是第一個字符,則在字符串的開頭。 我確信有一種更簡潔的方法,但它對我有用。 :) Function CusVlookup(pValue As String, pWorkRng As Range, pIndex As Long) Dim rng As Range Dim xResult As String xResult = "" For Each rng in pWorkRng If rng = pValue Then xResult = xResult & ";" & rng.Offset(1, pIndex - 0) If Left(xResult, 1) = ";" 然後 xResult = MID(xResult,1) End If End If Next CusVlookup = xResult End Function
網站主持人對此評論進行了最小化
如果為空,則為結果設置 if 條件。

函數 CusVlookup(lookupval, lookuprange As Range, indexcol As Long)
'更新 Extendoffice 20151118
暗淡 x 作為範圍
暗淡結果為字符串
結果 = ""
對於每個 x 在查找範圍內
如果 x = lookupval 則
如果不是結果 = "" 那麼
結果 = 結果 & " " & x.Offset(0, indexcol - 1)
其他
結果 = x.Offset(0, indexcol - 1)
如果結束
下一個x
CusVlookup = 結果
函數結束
網站主持人對此評論進行了最小化
這太棒了,但我正在尋找其他東西,我有一張帶有 RollNo StudentName sub1, sub2, sub3 ... 總結果的表,當我輸入 Rollnumber 時,它應該給出類似“SName Sub1 64, sub2 78,...總計389,結果通過”,可以嗎
網站主持人對此評論進行了最小化
有沒有辦法刪除連接中的重複值?
網站主持人對此評論進行了最小化
你好,雅各布,
可能下面的文章可以幫助您解決您的問題。
https://www.extendoffice.com/documents/excel/3381-excel-extract-unique-values-with-criteria.html

請嘗試,希望對您有所幫助!
網站主持人對此評論進行了最小化
有沒有辦法使用上面的 vba 代碼和公式只列出一次重複值? 我不確定將 countif>1 語句放在公式欄中或 vba 本身的什麼位置。 請幫忙
網站主持人對此評論進行了最小化
您可以添加兩個額外的條件來跳過空白單元格並跳過重複項:For i = 1 To CriteriaRange.Count
如果 CriteriaRange.Cells(i).Value = Condition 那麼
If ConcatenateRange.Cells(i).Value <> "" Then 'SKIP BANKS
If InStr(xResult, ConcatenateRange.Cells(i).Value) = 0 Then 'SKIP IF FOUND DUPLICATE
xResult = xResult & Separator & ConcatenateRange.Cells(i).Value
如果結束
如果結束
如果結束
接下來,我
網站主持人對此評論進行了最小化
我不得不說,我一直在嘗試獲得一個用於組合多個值並將它們返回到單個單元格的公式 2 天。 這個“如何”救了我!! 太感謝了! 如果沒有你的模塊,我永遠不會得到它!
不過,我確實有 2 個問題。 我將分隔符作為逗號而不是空格,因此它以逗號開頭。 有沒有辦法防止逗號開始但保留其餘部分?
我的第二個問題是; 當我使用填充句柄時,它會更改範圍值以及我要查找的單元格值。 我希望它繼續更改我要查找的單元格編號,但保持相同的範圍值。 我怎樣才能做到這一點?

非常感謝你的幫助!!
網站主持人對此評論進行了最小化
cusVlookup 對我來說非常有用。 使用不同分隔符的另一種方法是包裝兩個替代函數。 第一個(從內到外)用無空格替換第一個空格,第二個用我的“/”替換所有其他空格。 如果需要逗號,可以使用“,”。
=SUBSTITUTE(SUBSTITUTE(cusVlookup(D2,Table1,2)," ","",1)," "," / ")

此外,如果您的查找值不是第一列,您可以使用 0 或負數轉到左側的列。
=SUBSTITUTE(SUBSTITUTE(cusVlookup(D2,Table1,-1)," ","",1)," "," / ")
網站主持人對此評論進行了最小化
嗨,傑夫,
謝謝你的分享,你一定是個熱心人。
網站主持人對此評論進行了最小化
這對我來說非常有用 - 有沒有辦法改變它檢查單元格是否包含而不是完全匹配? 基本上我有一個任務列表,其中:
A 列:依賴項(例如 10003 10004 10008)
B 列:任務參考(例如 10001)
C 列:相關任務(公式結果的列)- 它將在其中查找任務引用以查看 A 列中哪些行包含它,然後列出這些任務的任務引用。

例如:

行 | A欄| B欄| C欄
1 | | 10001 | 10002 10003
2 | 10001 | 10002 | 10003
3 | 10001 10002 | 10003 |
網站主持人對此評論進行了最小化
您可能想使用 Instr() 函數來檢查單元格中的文本字符串中的某些內容。 如果您正在尋找開始或結束的詳細信息,也可以使用 Left() 和 Right()。
網站主持人對此評論進行了最小化
有沒有辦法獲得“class1”的唯一“名稱”
網站主持人對此評論進行了最小化
你好,西姆約翰,
也許下面的文章可以解決您的問題,請查看:
https://www.extendoffice.com/documents/excel/3381-excel-extract-unique-values-with-criteria.html
這裡還沒有評論
留下你的意見
以訪客身份發帖
×
評價此帖子:
0   字符
推薦地點

關注我們

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