Excelで単一セルに複数の値を返すVLOOKUPの方法は？

VLOOKUPはExcelで強力な関数ですが、デフォルトでは最初に一致する値しか返しません。すべての一致する値を取得して1つのセルに結合したい場合はどうすればよいでしょうか？これはデータセットを分析したり情報を要約したりする際に一般的な要件です。このガイドでは、数式と便利な機能を使用して複数の値を単一セルに返すためのステップバイステップの方法を説明します。

TEXTJOIN関数を使用して複数の値を1つのセルに返す（Excel 2019およびOffice 365）

• すべての一致する値を1つのセルに返す
• 重複なしですべての一致する値を1つのセルに返す

Kutoolsを使用して複数の値を1つのセルに返す

ユーザー定義関数を使用して複数の値を1つのセルに返す

• すべての一致する値を1つのセルに返す
• 重複なしですべての一致する値を1つのセルに返す

TEXTJOIN関数を使用して複数の値を1つのセルに返す（Excel 2019およびOffice 365）

Excel 2019やOffice 365のような高バージョンのExcelを使用している場合、新しい関数「TEXTJOIN」があります。この強力な関数を使用すると、すべての一致する値を迅速に検索し、1つのセルに返すことができます。

  すべての一致する値を1つのセルに返す

結果を表示したい空白セルに以下の数式を適用し、「Ctrl + Shift + Enter」キーを同時に押して最初の結果を得ます。その後、フィルハンドルをドラッグして他のセルにこの数式を適用すると、以下のようにすべての対応する値が表示されます：

  重複なしですべての一致する値を1つのセルに返す

検索データに基づいて重複なしですべての一致する値を返したい場合、以下の数式が役立つかもしれません。

以下の数式をコピーして空白セルに貼り付け、「Ctrl + Shift + Enter」キーを同時に押して最初の結果を得ます。その後、この数式を他のセルにコピーすると、以下のように重複しないすべての対応する値が表示されます：

Kutoolsを使用して複数の値を1つのセルに返す

Kutools for Excelの「高度な行のマージ」機能を使用すると、複雑な数式を使わずに複数の一致する値を簡単に1つのセルに取得できます！手動での作業を忘れて、より効率的な方法でExcelでの検索タスクを処理しましょう。Kutools for Excelがどのようにそれを可能にするかを見てみましょう！

Kutools for Excelをインストール後、次の手順を行ってください：

1. 結合したい列に基づいたデータ範囲を選択します。

2. 「Kutools」＞「統合と分割」＞「高度な行のマージ」をクリックします。スクリーンショットをご覧ください：

3. 表示された「高度な行のマージ」ダイアログボックスで：

• 結合する基準となる列名をクリックし、「プライマリキー」をクリックします。
• 次に、キーカラムに基づいてデータを結合したい別の列をクリックし、「操作」フィールドのドロップダウンリストから結合データを区切るための区切り文字を選択します。
• 次に、「OK」ボタンをクリックします。

同じ値に基づいて、もう一方の列からのすべての対応する値が1つのセルに結合されます。スクリーンショットをご覧ください：

ヒント: セルを結合する際に重複した内容を削除したい場合は、ダイアログボックス内の「重複値を削除する」オプションをチェックしてください。これにより、重複を排除した一意のエントリのみが1つのセルに結合され、データがより整理されてクリーンになります。スクリーンショットをご覧ください：

今すぐKutools for Excelをダウンロードして無料でお試しください！

ユーザー定義関数を使用して複数の値を1つのセルに返す

上記のTEXTJOIN関数はExcel 2019およびOffice 365でのみ利用可能です。他の古いバージョンのExcelを使用している場合は、いくつかのコードを使用してこのタスクを完了する必要があります。

  すべての一致する値を1つのセルに返す

1. 「ALT + F11」キーを押すと、「Microsoft Visual Basic for Applications」ウィンドウが開きます。

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

VBAコード: 複数の値を1つのセルに返す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つのセルにすべての対応する値を取得します。スクリーンショットをご覧ください：

  重複なしですべての一致する値を1つのセルに返す

返される一致する値から重複を無視するには、以下のコードを使用してください。

1. 「Alt + F11」キーを押して「Microsoft Visual Basic for Applications」ウィンドウを開きます。

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

VBAコード: 重複しない複数の一致する値を1つのセルに返す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のヒントやコツに興味がある場合、当社のウェブサイトには数千ものチュートリアルが用意されています。

関連記事：