メインコンテンツへスキップ

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

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

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

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

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


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

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

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

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

=TEXTJOIN(",",TRUE,IF($A$2:$A$11=E2,$C$2:$C$11,""))

注: 上記の式では、 A2:A11 ルックアップ範囲にはルックアップデータが含まれています。 E2 ルックアップ値です。 C2:C11 一致する値を返すデータ範囲です。 ",「」は、複数のレコードを区切るための区切り文字です。

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

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

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

=TEXTJOIN(",", TRUE, IF(IFERROR(MATCH($C$2:$C$11, IF(E2=$A$2:$A$11, $C$2:$C$11, ""), 0),"")=MATCH(ROW($C$2:$C$11), ROW($C$2:$C$11)), $C$2:$C$11, ""))

注: 上記の式では、 A2:A11 ルックアップ範囲にはルックアップデータが含まれています。 E2 ルックアップ値です。 C2:C11 一致する値を返すデータ範囲です。 ",「」は、複数のレコードを区切るための区切り文字です。

ユーザー定義関数を使用して複数の値を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つのセルに対応するすべての値を取得します。スクリーンショットを参照してください。

注: 上記の式では、 A2:A11 ルックアップ範囲にはルックアップデータが含まれています。 E2 ルックアップ値です。 C2:C11 一致する値を返すデータ範囲です。 ",「」は、複数のレコードを区切るための区切り文字です。

重複することなくすべての一致する値を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:

注: 上記の式では、 A2:C11 使用するデータ範囲は、 E2 ルックアップ値、数値です 3 戻り値を含む列番号です。

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

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

注:これを適用する 高度な結合行、まず、ダウンロードする必要があります Kutools for Excel、次に機能をすばやく簡単に適用します。

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

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

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

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

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

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

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


より相対的な記事:

  • いくつかの基本的な例と高度な例を含むVLOOKUP関数
  • Excelでは、VLOOKUP関数はほとんどのExcelユーザーにとって強力な関数であり、データ範囲の左端の値を検索し、指定した列から同じ行の一致する値を返すために使用されます。 このチュートリアルでは、Excelのいくつかの基本的な例と高度な例でVLOOKUP関数を使用する方法について説明します。
  • XNUMXつまたは複数の基準に基づいて複数の一致する値を返します
  • 通常、VLOOKUP関数を使用すると、特定の値を検索して一致するアイテムを返すのは簡単です。 しかし、XNUMXつ以上の基準に基づいて複数の一致する値を返そうとしたことがありますか? この記事では、Excelでこの複雑なタスクを解決するためのいくつかの式を紹介します。
  • Vlookupと複数の値を垂直方向に返す
  • 通常、Vlookup関数を使用して最初の対応する値を取得できますが、特定の基準に基づいて一致するすべてのレコードを返したい場合もあります。 この記事では、vlookupして、一致するすべての値を垂直方向、水平方向、またはXNUMXつのセルに返す方法について説明します。
  • Vlookupとドロップダウンリストから複数の値を返す
  • Excelで、ドロップダウンリストから複数の対応する値をvlookupして返すにはどうすればよいですか。つまり、ドロップダウンリストからXNUMXつの項目を選択すると、そのすべての相対値が一度に表示されます。 この記事では、ソリューションを段階的に紹介します。

最高のオフィス生産性向上ツール

人気の機能: 重複を検索、強調表示、または識別する   |  空白行を削除する   |  データを失わずに列またはセルを結合する   |   数式なしのラウンド ...
スーパールックアップ: 複数の基準の VLookup    複数の値の VLookup  |   複数のシートにわたる VLookup   |   ファジールックアップ ....
詳細ドロップダウン リスト: ドロップダウンリストを素早く作成する   |  依存関係のドロップダウン リスト   |  複数選択のドロップダウンリスト ....
列マネージャー: 特定の数の列を追加する  |  列の移動  |  Toggle 非表示列の表示ステータス  |  範囲と列の比較 ...
注目の機能: グリッドフォーカス   |  デザインビュー   |   ビッグフォーミュラバー    ワークブックとシートマネージャー   |  リソースライブラリ (自動テキスト)   |  日付ピッカー   |  ワークシートを組み合わせる   |  セルの暗号化/復号化    リストごとにメールを送信する   |  スーパーフィルター   |   特殊フィルター (太字/斜体/取り消し線をフィルター...) ...
上位 15 のツールセット12 テキスト 工具 (テキストを追加, 文字を削除する、...)   |   50+ チャート 種類 (ガントチャート、...)   |   40+ 実用的 (誕生日に基づいて年齢を計算する、...)   |   19 挿入 工具 (QRコードを挿入, パスから画像を挿入、...)   |   12 変換 工具 (数字から言葉へ, 通貨の換算、...)   |   7 マージ&スプリット 工具 (高度な結合行, 分割セル、...)   |   ... もっと

Kutools for Excel で Excel スキルを強化し、これまでにない効率を体験してください。 Kutools for Excelは、生産性を向上させ、時間を節約するための300以上の高度な機能を提供します。  最も必要な機能を入手するにはここをクリックしてください...

kteタブ201905


Officeタブは、タブ付きのインターフェイスをOfficeにもたらし、作​​業をはるかに簡単にします

  • Word、Excel、PowerPointでタブ付きの編集と読み取りを有効にする、パブリッシャー、アクセス、Visioおよびプロジェクト。
  • 新しいウィンドウではなく、同じウィンドウの新しいタブで複数のドキュメントを開いて作成します。
  • 生産性を 50% 向上させ、毎日何百回もマウス クリックを減らすことができます!
Comments (43)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
I have created a problem.
"I" have combined a "Textjoin" end "Vlookup" to return multiple values in to one single cell.
My problem is that the formula have to have an exact value to look for and I want it to lookup an "almost" match or Partial match.

Example: I have made a schedule how we ate going to work and a D1 is working from 07:30-16:00. And to lookup D1 is not the problem, the problem is that my boss sometimes puts other stuff togeather with the D1... Like "D1 +" or "D1 meeting".
Since my formula only lookup "D1" it misses for example the "D1 +".

My formula (that I have gotten from the web) =TEXTJOIN(" och ";SANT;OM($B$3:$B$15=$C$22:$F$22;$A$3:$A$15;""))It´s in swedish so "SANT" is "TRUE" and "OM" is "IF".

How can I make the formula lookup all the cells that have some form of "D1" in it and return all those to the same cell?
No matter if it says "D1 +" or "D1 meeting" or whatever.
The reson I want this, is because the boss always leave "D1" but can add other text behind the "D1"... and just because of that, my boss messes up my formula.
This comment was minimized by the moderator on the site
Hi!
This is a great VBA-Code which could help me a lot.But when I start the Function MultipleLookupNoRept Excel crashs...I´ve got a Dataset with about 6.000 Rows (Excel 2013).... is this too much for the VBA Function?

Thanks!
This comment was minimized by the moderator on the site
Hello Mr.XXL,Sorry to hear that. The row limit for Excel 2013 is 1048576. Therefore, maybe the VBA code is the reason for the crash.
Anyway, I would love to offer you another VBA code for Vlookup To Return All Matching Values Without Duplicates Into One Cell. Please use the VBA code below:
Option Explicit

Function Lookup_concat(Search_string As String, _
Search_in_col As Range, Return_val_col As Range)

Dim i As Long
Dim temp() As Variant
Dim result As String
ReDim temp(0)

For i = 1 To Search_in_col.Count
If Search_in_col.Cells(i, 1) = Search_string Then
temp(UBound(temp)) = Return_val_col.Cells(i, 1).Value
ReDim Preserve temp(UBound(temp) + 1)
End If
Next

If temp(0) <> "" Then
ReDim Preserve temp(UBound(temp) - 1)
Unique temp
For i = LBound(temp) To UBound(temp)
result = result & " " & temp(i)
Next i
Lookup_concat = Trim(result)
Else
Lookup_concat = ""
End If

End Function

Function Unique(tempArray As Variant)

Dim coll As New Collection
Dim Value As Variant

On Error Resume Next
For Each Value In tempArray
If Len(Value) > 0 Then coll.Add Value, CStr(Value)
Next Value
On Error GoTo 0

ReDim tempArray(0)

For Each Value In coll
tempArray(UBound(tempArray)) = Value
ReDim Preserve tempArray(UBound(tempArray) + 1)
Next Value

End Function

After you insert this VBA code in the Module, please type the formula =Lookup_concat(E2,$A$2:$A$14,$C$2:$C$14) into a blank cell where you want to output the result, and then drag the fill hanlde down to get all matching values. Please see the file I uploaded in this comment. Hope it solves your problem. 
Sincerely,Mandy

This comment was minimized by the moderator on the site
Hi, Thanks so much this worked!I used it to pull dates, that populated in the serial number format (<span style="letter-spacing: 0.2px; color: inherit; font-family: inherit; font-style: inherit; font-variant-ligatures: inherit; font-variant-caps: inherit; font-weight: inherit;">Changing the format to short date format using =TEXT(A2,”mm/dd/yy”) OR =DATEVALUE(A2) are not working. Do you have any solutions?</span>
This comment was minimized by the moderator on the site
Thank you for the explanations, however the function 'MultipleLookupNoRept' does not work on my file, could you tell me if an error exists.
This comment was minimized by the moderator on the site
Hi, Hasnae,Please check if you miss the third step -  check Microsoft Scripting Runtime option in the Available References list box.

This comment was minimized by the moderator on the site
Thank you so much for the code. Is there a way I can use the code to look up multiple values from multiple sheets? I tried to combine your function with IFERROR function but it doesn't seem to work.
This comment was minimized by the moderator on the site
Can this be modified to place the sum of those values? Instead of (400 400 400 400 400 400), can it sum them to show (2400)?
This comment was minimized by the moderator on the site
How with HLookUp function?
This comment was minimized by the moderator on the site
thanks for the code. I have modified it to allow you to optionally specify your own separator, Default is " ", if you specify the separator as"#cr" it will insert a CR/LF so the values will be on a separate line in the cell. It only applies the separator if there are multiple values

Function MYVLOOKUP(pValue As String, pWorkRng As Range, pIndex As Long, Optional ByVal pSep As Variant)

' ### Returns multiple values from a table into 1 cell ###

' pValue is the key value to lookup

' WorkRng is the Table you want to look up

' pIndex is the column # for the values to be returned from the pWorkRng

' pSep (optional) is the separator to be used. if omitted then default is a space (it doesn't apply the separator for the 1st entry)

' if the separtor = "#cr" it will separate the values on different line in the cell

Dim rng As Range

Dim sSep As String

Dim xResult As String

Dim Item1 As Boolean

Item1 = True



If IsMissing(pSep) = True Then

sSep = vbCr

Else

If pSep = "#cr" Then

sSep = vbCrLf

Else

sSep = pSep

End If

End If



xResult = ""

For Each rng In pWorkRng

If rng = pValue Then

If Item1 Then

xResult = xResult & rng.Offset(0, pIndex - 1)

Item1 = False

Else

xResult = xResult & sSep & rng.Offset(0, pIndex - 1)

End If

End If

Next

MYVLOOKUP = xResult

End Function
This comment was minimized by the moderator on the site
Thank you for this, the line breaks are what i needed to top this formula off! Question, is there a way to modify the code so that two values are compared? For example, similar to what we see with index and match, can i look for Product and Quantity columns, and based on those parameters it outputs results from the Region Column?
This comment was minimized by the moderator on the site
Thanks a lot for this code, it is very helpful. Does anyone know away to sum the values in the cell to just have at total of them.
Cheers
This comment was minimized by the moderator on the site
Hello, James, to sum values based on the corresponding items, the following article may help you, please chek it:
https://www.extendoffice.com/documents/excel/1268-excel-combine-duplicate-rows-and-sum.html
This comment was minimized by the moderator on the site
I have a server, it has connected with multiple applications. I want to compare compare two column and get the related applications details for that server.

What is the command for that.
There are no comments posted here yet
Load More
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations