如何在 Excel 中使用 vlookup 返回單一單元格中的多個值？

VLOOKUP 是 Excel 中一個強大的函數，但默認情況下，它只返回第一個匹配的值。如果你需要獲取所有匹配的值並將它們合併到一個單元格中該怎麼辦呢？這在分析數據集或總結信息時是一個常見的需求。在本指南中，我們將逐步介紹如何使用公式和實用功能將多個值返回到單一單元格中。

使用 TEXTJOIN 函數將多個值返回到一個單元格（Excel 2019 和 Office 365）

• 將所有匹配的值返回到一個單元格
• 將所有不重複的匹配值返回到一個單元格

使用 Kutools 將多個值返回到一個單元格

使用用戶定義函數將多個值返回到一個單元格

• 將所有匹配的值返回到一個單元格
• 將所有不重複的匹配值返回到一個單元格

使用 TEXTJOIN 函數將多個值返回到一個單元格（Excel 2019 和 Office 365）

如果你使用的是較高版本的 Excel，例如 Excel 2019 和 Office 365，有一個新函數 - TEXTJOIN，通過這個強大的函數，你可以快速進行 vlookup 並返回所有匹配的值到一個單元格中。

  將所有匹配的值返回到一個單元格

請將以下公式應用到你想放置結果的空白單元格中，然後按 Ctrl + Shift + Enter 鍵一起以獲得第一個結果，然後拖動填充柄向下到你想要使用的單元格，你將獲得如下截圖所示的所有對應值：

  將所有不重複的匹配值返回到一個單元格

如果你想根據查找數據返回所有匹配的值且不包含重複項，下面的公式可能會幫助你。

請將以下公式複製並粘貼到空白單元格中，然後按 Ctrl + Shift + Enter 鍵一起以獲得第一個結果，然後複製此公式填滿其他單元格，你將獲得如下截圖所示的所有對應值且沒有重複項：

使用 Kutools 將多個值返回到一個單元格

借助 Kutools for Excel 的「高級合併行」功能，你可以輕鬆地將多個匹配值檢索到單一單元格中——無需複雜的公式！告別手動操作，解鎖更高效的方式來處理你在 Excel 中的查找任務。讓我們來看看 Kutools for Excel 如何實現這一切！

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

1. 選擇你希望根據另一列合併一列數據的數據範圍。

2. 點擊「Kutools」 > 「合併與拆分」 > 「高級合併行」，見截圖：

3. 在彈出的「高級合併行」對話框中：

• 點擊要基於其進行合併的關鍵列名稱，然後點擊「主鍵」。
• 然後點擊另一列，你希望根據關鍵列合併其數據，並從「計算」字段的下拉列表中選擇一個區分符以分隔合併的數據，從「合併」部分選擇。
• 然後，點擊確定按鈕。

來自另一列的所有對應值，基於相同的值，被合併到單一單元格中。見截圖：

提示：如果在合併單元格時想刪除重複內容，只需在對話框中勾選「刪除重複值」選項即可。這確保只有唯一的條目被合併到單一單元格中，使你的數據更加整潔有序，而無需額外努力。見截圖：

立即下載並免費試用 Kutools for Excel！

使用用戶定義函數將多個值返回到一個單元格

上述 TEXTJOIN 函數僅適用於 Excel 2019 和 Office 365，如果你使用的是其他較低版本的 Excel，則應該使用一些代碼來完成此任務。

  將所有匹配的值返回到一個單元格

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

2. 點擊「插入」 > 「模塊」，並將以下代碼粘貼到模塊窗口中。

VBA 代碼：Vlookup 返回多個值到一個單元格

Function ConcatenateIf(CriteriaRange As Range, Condition As Variant, ConcatenateRange As Range, Optional Separator As String = ",") As Variant
'Updateby Extendoffice
Dim xResult As String
On Error Resume Next
If CriteriaRange.Count <> ConcatenateRange.Count Then
    ConcatenateIf = CVErr(xlErrRef)
    Exit Function
End If
For i = 1 To CriteriaRange.Count
    If CriteriaRange.Cells(i).Value = Condition Then
        xResult = xResult & Separator & ConcatenateRange.Cells(i).Value
    End If
Next i
If xResult <> "" Then
    xResult = VBA.Mid(xResult, VBA.Len(Separator) + 1)
End If
ConcatenateIf = xResult
Exit Function
End Function

3. 然後保存並關閉此代碼，返回工作表，並輸入此公式： =CONCATENATEIF($A$2:$A$11, E2, $C$2:$C$11, ", ") 到你希望放置結果的特定空白單元格中，然後拖動填充柄向下以獲取你希望的一個單元格中的所有對應值，見截圖：

  將所有不重複的匹配值返回到一個單元格

要忽略返回匹配值中的重複項，請使用以下代碼。

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

2. 點擊「插入」 > 「模塊」，並將以下代碼粘貼到模塊窗口中。

VBA 代碼：Vlookup 並返回多個唯一匹配值到一個單元格

Function MultipleLookupNoRept(Lookupvalue As String, LookupRange As Range, ColumnNumber As Integer)
'Updateby Extendoffice
    Dim xDic As New Dictionary
    Dim xRows As Long
    Dim xStr As String
    Dim i As Long
    On Error Resume Next
    xRows = LookupRange.Rows.Count
    For i = 1 To xRows
        If LookupRange.Columns(1).Cells(i).Value = Lookupvalue Then
            xDic.Add LookupRange.Columns(ColumnNumber).Cells(i).Value, ""
        End If
    Next
    xStr = ""
    MultipleLookupNoRept = xStr
    If xDic.Count > 0 Then
        For i = 0 To xDic.Count - 1
            xStr = xStr & xDic.Keys(i) & ","
        Next
        MultipleLookupNoRept = Left(xStr, Len(xStr) - 1)
    End If
End Function

3. 插入代碼後，然後點擊「工具」 > 「引用」在打開的「Microsoft Visual Basic for Applications」窗口中，然後，在彈出的「引用 – VBAProject」對話框中，勾選「可用引用」列表框中的「Microsoft Scripting Runtime」選項，見截圖：

4. 然後點擊確定關閉對話框，保存並關閉代碼窗口，返回工作表，並輸入此公式： =MultipleLookupNoRept(E2,$A$2:$C$11,3) 到你希望輸出結果的空白單元格中，然後拖動填充柄向下以獲取所有匹配值，見截圖：

無論你選擇像 TEXTJOIN 與數組函數結合的公式、利用像 Kutools for Excel 或用戶定義函數這樣的工具，所有方法都有助於簡化複雜的查找任務。選擇最適合你需求的方法。如果你有興趣探索更多 Excel 技巧，我們的網站提供了成千上萬的教程。

更多相關文章：