ExcelのXNUMXつのセルに複数の値を返すようにvlookupする方法は？

通常、Excelでは、VLOOKUP関数を使用するときに、条件に一致する値が複数ある場合は、最初の値を取得できます。 しかし、次のスクリーンショットのように、基準を満たすすべての対応する値をXNUMXつのセルに返したい場合があります。どうすれば解決できますか？

TEXTJOIN関数を使用して複数の値を2019つのセルに返すVlookup（Excel365およびOfficeXNUMX）

• 一致するすべての値をXNUMXつのセルに返すVlookup
• 重複することなくすべての一致する値をXNUMXつのセルに返すVlookup

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

• 一致するすべての値をXNUMXつのセルに返すVlookup
• 重複することなくすべての一致する値をXNUMXつのセルに返すVlookup

便利な機能を備えたXNUMXつのセルに複数の値を返すVlookup

TEXTJOIN関数を使用して複数の値を2019つのセルに返すVlookup（Excel365およびOfficeXNUMX）

Excel2019やOffice365などの上位バージョンのExcelを使用している場合は、新しい機能があります- テキスト結合、この強力な関数を使用すると、一致するすべての値をすばやくvlookupしてXNUMXつのセルに返すことができます。

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

結果を入力する空白のセルに次の数式を適用して、を押してください Ctrl + Shift + Enter キーを一緒に押して最初の結果を取得し、塗りつぶしハンドルをこの数式を使用するセルまでドラッグすると、以下のスクリーンショットに示すように、対応するすべての値が取得されます。

  重複することなくすべての一致する値をXNUMXつのセルに返すVlookup

重複することなくルックアップデータに基づいて一致するすべての値を返したい場合は、次の式が役立つ場合があります。

次の数式をコピーして空白のセルに貼り付け、を押してください Ctrl + Shift + Enter キーを合わせて最初の結果を取得し、この数式をコピーして他のセルに入力すると、以下のスクリーンショットに示すように、重複する値なしで対応するすべての値が取得されます。

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

上記のTEXTJOIN関数は、Excel2019およびOffice365でのみ使用できます。他の下位バージョンのExcelを使用している場合は、このタスクを完了するためにいくつかのコードを使用する必要があります。

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

1。 を押し続けます Alt + F11 キー、そしてそれは開きます アプリケーション向け Microsoft Visual Basic 窓。

2に設定します。 OK をクリックします。 インセット > モジュール、次のコードをに貼り付けます モジュールウィンドウ.

VBAコード：XNUMXつのセルに複数の値を返す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, ", ") 結果を配置する特定の空白のセルに移動し、塗りつぶしハンドルを下にドラッグして、必要なXNUMXつのセルに対応するすべての値を取得します。スクリーンショットを参照してください。

  重複することなくすべての一致する値をXNUMXつのセルに返すVlookup

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

1。 を押し続けます Altキー+ F11 キーを押して アプリケーション向け Microsoft Visual Basic 窓。

2に設定します。 OK をクリックします。 インセット > モジュール、次のコードをに貼り付けます モジュールウィンドウ.

VBAコード：Vlookupを実行し、複数の一意の一致値をXNUMXつのセルに返します

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 ウィンドウ、そして、ポップアウトで 参照– VBAProject ダイアログボックス、チェック Microsoftスクリプトランタイム 内のオプション 利用可能な参考文献 リストボックス、スクリーンショットを参照してください：

4。 次に、をクリックします OK ダイアログボックスを閉じるには、コードウィンドウを保存して閉じ、ワークシートに戻り、次の式を入力します。 =MultipleLookupNoRept(E2,$A$2:$C$11,3) into a blank cell where you want to output the result, and then drag the fill hanlde down to get all matching values, see screenshot:

便利な機能を備えたXNUMXつのセルに複数の値を返すVlookup

 あなたが私たちを持っているなら Kutools for Excelそのと 高度な結合行 この機能を使用すると、同じ値に基づいて行をすばやくマージまたは結合し、必要に応じていくつかの計算を実行できます。

インストールした後 Kutools for Excel、次のようにしてください。

1。 別の列に基づいてXNUMXつの列データを結合するデータ範囲を選択します。

2に設定します。 OK をクリックします。 クツール > マージ＆スプリット > 高度な結合行、スクリーンショットを参照してください：

3。 飛び出した 高度な結合行 ダイアログボックス：

• 結合するキー列名をクリックしてから、 主キー
• 次に、キー列に基づいてデータを結合する別の列をクリックし、をクリックします。 組み合わせる 結合されたデータを分離するためにXNUMXつのセパレータを選択します。

4. 次に、をクリックします。 OK ボタンをクリックすると、次の結果が得られます。

今すぐExcel用のKutoolsをダウンロードして無料トライアル！

より相対的な記事：