ExcelでVLOOKUPを使用して複数の対応する値を連結するにはどうすればよいですか？

ExcelでVLOOKUPを使用する場合、関数は通常、指定された検索条件に対して最初に一致した値のみを返します。しかし、クラス内のすべての学生をリスト化したり、特定のカテゴリに関連するすべての製品を表示したりするなど、特定のキーに関連付けられたすべての一致する値を取得して結合する必要がある一般的なシナリオが数多くあります。標準のVLOOKUP関数はこの点で制限されているため、複数の対応する結果を検索して1つのセルに連結する方法を模索するかもしれません。以下では、異なるExcelバージョンやユーザーの好みに応じた、このタスクを達成するためのいくつかの実用的で効率的な方法を探ります。

ExcelでVLOOKUPを使用して複数の対応する値を連結する

• TEXTJOINとFILTER関数の使用（Excel 365/2021）
• Kutools for Excelの使用
• ユーザー定義関数の使用
• 複数の連結を行うためのVBAコードの使用

TEXTJOINとFILTER関数を使用してVLOOKUPを行い、複数の対応する値を連結する

Excel 365またはExcel 2021を使用している場合、TEXTJOINとFILTER関数の組み合わせにより、すべての一致する値を検索して連結する効率的な、数式ベースのアプローチが提供されます。このソリューションは特に動的で更新頻度の高いデータセットに適しています。なぜなら、ソースデータが変更されると結果が自動的に更新されるからです。FILTER関数に対応している最新のOfficeバージョンでの使用が最適です。

ターゲットセルに以下の数式を入力し、他の行にも適用したい場合は数式を下にドラッグします。すべての一致する値が抽出され、1つのセルに結合されます。スクリーンショットをご覧ください：

=TEXTJOIN(", ", TRUE, FILTER($B$2:$B$16, $A$2:$A$16=D2, ""))

Kutools for Excelを使用してVLOOKUPを行い、複数の対応する値を連結する

組み込みの数式メソッドが難しいと感じる場合や、使用しているExcelのバージョンがTEXTJOINやFILTERのような高度な関数をサポートしていない場合、Kutools for Excelはユーザーフレンドリーなグラフィカルソリューションを提供します。Kutoolsの一対多の検索機能により、わずか数ステップで複数の一致結果を検索して連結することが可能であり、初心者から上級者まで幅広いユーザーに適しています。Kutoolsを使用すると、複雑な数式やコードを書く必要がなく、繰り返しの検索や集計が必要な大規模または可変的なデータセットを扱う際に特に便利です。

Kutools for Excelをインストール後、以下の手順に従ってください：

Kutools > スーパーLOOKUP > 一対多の検索（複数の結果を返す）をクリックして設定ダイアログを開きます。このダイアログ内で、以下の手順を使用して検索と出力設定を迅速に構成できます：

1. 連結された結果のターゲット出力セルを選択し、検索したい値を含むセルを選択します；
2. 検索キーと結果列の両方を含むテーブル範囲を示します；
3. 検索キーを含む列（キーカラム）と連結される値を含む列（返却列）を指定します；
4. OKボタンをクリックして設定を確認し、データを処理します。
   

結果Kutoolsは、選択した出力セルにすべての一致する連結された値を表示します。スクリーンショットをご覧ください：

この方法は、複雑な数式やコードを使わずにExcelインターフェースから作業することを好むユーザーにお勧めです。また、数式エラーの可能性を減らし、繰り返しの検索や連結タスクを処理する際の生産性を向上させます。

ユーザー定義関数を使用してVLOOKUPを行い、複数の対応する値を連結する

VBA（Visual Basic for Applications）に精通しているユーザー、または動的配列やFILTER関数のサポートがない古いExcelバージョンを使用しているユーザーは、カスタムのユーザー定義関数（UDF）を作成して、柔軟な複数結果の連結を実現できます。この方法はすべてのExcelバージョンと互換性があり、特定の区切り文字や条件に合わせて調整できます。

ALT + F11キーを押してMicrosoft Visual Basic for Applicationsウィンドウを開きます。

挿入 > モジュールをクリックし、次のコードをモジュールウィンドウに貼り付けます。

VBAコード: VLOOKUPを行い、セル内の複数の一致する値を連結する

Function ConcatenateMatches(LookupValue As String, LookupRange As Range, ReturnRange As Range, Optional Delimiter As String = ", ") As String
'Updateby Extendoffice
    Dim Cell As Range
    Dim Result As String
    Result = ""
    For Each Cell In LookupRange
        If Cell.Value = LookupValue Then
            Result = Result & Cell.Offset(0, ReturnRange.Column - LookupRange.Column).Value & Delimiter
        End If
    Next Cell
    If Result <> "" Then
        Result = Left(Result, Len(Result) - Len(Delimiter))
    End If
    ConcatenateMatches = Result
End Function

VBAエディターを保存して閉じます。ワークシートに戻り、空白のセルに =ConcatenateMatches(D2, $A$2:$A$16, $B$2:$B$16) という数式を入力してこのUDFを使用します。必要な場合は、フィルハンドルを下にドラッグして数式を他のセルにコピーします。特定の検索値に基づくすべての対応する値が返され、カンマとスペースで区切られて1つのセルに連結されます。スクリーンショットをご覧ください：

VBAコードを使用してVLOOKUPを行い、複数の対応する値を連結する

繰り返し使用が必要な場合や、ワークシートセル内にカスタム関数を使用したくない場合、準備済みのVBAマクロを使用して直接結果を連結できます。この方法は、すべてのユーザーが同じバージョンやアドインを持っているわけではない共有環境でうまく機能します。

開発ツール > Visual Basic をクリックしてVBAエディターを開きます。

VBAウィンドウで挿入 > モジュールをクリックし、次のコードをモジュールに貼り付けます：

Sub VLookupAndConcatenate()
    Dim ws As Worksheet
    Dim dataRange As Range, lookupRange As Range, resultRange As Range
    Dim dict As Object
    Dim i As Long, lastRow As Long
    Dim lookupValue As Variant, result As String
    Dim delimiter As String
    delimiter = ", "
    Set dict = CreateObject("Scripting.Dictionary")
    Set ws = ActiveSheet
    On Error Resume Next
    Set dataRange = Application.InputBox( _
        Prompt:="Please select the data range (contains lookup column and result column)", _
        Title:="Select Data Range", _
        Type:=8)
    On Error GoTo 0
    If dataRange Is Nothing Then Exit Sub
    On Error Resume Next
    Set lookupRange = Application.InputBox( _
        Prompt:="Please select the lookup range (single column)", _
        Title:="Select Lookup Range", _
        Type:=8)
    On Error GoTo 0
    If lookupRange Is Nothing Then Exit Sub
    On Error Resume Next
    Set resultRange = Application.InputBox( _
        Prompt:="Please select the starting cell for results output", _
        Title:="Select Output Location", _
        Type:=8)
    On Error GoTo 0
    If resultRange Is Nothing Then Exit Sub
    resultRange.Resize(lookupRange.Rows.Count, 1).ClearContents
    For i = 1 To dataRange.Rows.Count
        lookupValue = dataRange.Cells(i, 1).Value
        If Not dict.Exists(lookupValue) Then
            dict.Add lookupValue, dataRange.Cells(i, 2).Value
        Else
            dict(lookupValue) = dict(lookupValue) & delimiter & dataRange.Cells(i, 2).Value
        End If
    Next i
    For i = 1 To lookupRange.Rows.Count
        lookupValue = lookupRange.Cells(i, 1).Value
        If dict.Exists(lookupValue) Then
            resultRange.Cells(i, 1).Value = dict(lookupValue)
        Else
            resultRange.Cells(i, 1).Value = "Not Found"
        End If
    Next i
    MsgBox "Operation completed! Processed " & lookupRange.Rows.Count & " lookup values.", vbInformation
End Sub

マクロを実行するための ボタンをクリックします。入力ボックスがデータ範囲、検索範囲、結果範囲の選択を促します。連結された結果は選択された出力セルに直接表示されます。

このマクロアプローチは、さまざまな値で複数の連結検索を頻繁に行う場合に特に役立ちます。これにより、UDF呼び出しでワークシートが散らかるのを防げます。

必要に応じてコード内の区切り文字を簡単に調整でき、ワークフローに応じて結果をセルやファイルに出力するようにマクロを拡張できます。

Excelで複数の対応する値を連結することは、さまざまなアプローチで可能です。それぞれの方法には状況に応じた具体的な利点があります。動的配列の数式、Kutools for Excelのようなアドイン、またはVBAベースの方法のいずれを選択しても、グループ化されたデータを効率的に分析して表示する能力が向上します。データセットのサイズや複雑さに応じて、自分やチームにとって最適なパフォーマンスと保守の容易さを考慮して方法を選んでください。日常操作では、データの一貫性を確認し、結合されたセルを避け、参照範囲を検証して最良の結果を得ましょう。数式計算でエラーが発生した場合は、範囲がデータに一致していることと、Excelバージョンに応じた正しい数式入力方法を使用していることを再確認してください。

さらに高度なExcel技術や幅広い実用的なハウツーガイドについては、当社の広範なチュートリアルライブラリをご覧ください。