Note: The other languages of the website are Google-translated. Back to English
English English▼
X
en Englishar Arabichy Armenianzh-CN Chinese (Simplified)zh-TW Chinese (Traditional)cs Czechda Danishnl Dutchfr Frenchde Germanel Greekhu Hungarianid Indonesianga Irishit Italianja Japaneseko Koreanpl Polishpt Portuguesero Romanianru Russiansl Slovenianes Spanishsv Swedishth Thaitr Turkishuk Ukrainianvi Vietnamesecy Welsh

How to vlookup to return multiple values in one cell in Excel?

Normally, in Excel, when you use the VLOOKUP function, if there are multiple values to match the criteria, you just can get the first one. But, sometimes, you want to return all the corresponding values which meet the criteria into one cell as following screenshot shown, how could you solve it?

Vlookup to return multiple values into one cell with TEXTJOIN function (Excel 2019 and Office 365)

Vlookup to return multiple values into one cell with User Defined Function

Vlookup to return multiple values into one cell with a useful feature

Vlookup to return multiple values into one cell with TEXTJOIN function (Excel 2019 and Office 365)

If you have the higher version of the Excel such as Excel 2019 and Office 365, there is a new function - TEXTJOIN, with this powerful function, you can quickly vlookup and return all matching values into one cell.

Vlookup to return all matching values into one cell

Please apply the below formula into a blank cell where you want to put the result, then press Ctrl + Shift + Enter keys together to get the first result, and then drag the fill handle down to the cell you want to use this formula, and you will get all corresponding values as below screenshot shown:

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

Note: In the above formula, A2:A11 is the lookup range contains the lookup data, E2 is the lookup value, C2:C11 is the data range that you want to return the matching values from, "," is the separator to separate the multiple records.
Vlookup to return all matching values without duplicates into one cell

If you want to return all matching values based on the lookup data without duplicates, the below formula may help you.

Please copy and paste the following formula into a blank cell, then press Ctrl + Shift + Enter keys together to get the first result, and then copy this formula to fill other cells, and you will get all corresponding values without the dulpicate ones as below screenshot shown:

=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, ""))

Note: In the above formula, A2:A11 is the lookup range contains the lookup data, E2 is the lookup value, C2:C11 is the data range that you want to return the matching values from, "," is the separator to separate the multiple records.

Vlookup to return multiple values into one cell with User Defined Function

The above TEXTJOIN function is only available for Excel 2019 and Office 365, if you have other lower Excel versions, you should use some codes for finishing this task.

Vlookup to return all matching values into one cell

1. Hold down the ALT + F11 keys, and it opens the Microsoft Visual Basic for Applications window.

2. Click Insert > Module, and paste the following code in the Module Window.

VBA code: Vlookup to return multiple values into one cell

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. Then save and close this code, go back to the worksheet, and enter this formula: =CONCATENATEIF($A$2:$A$11, E2, $C$2:$C$11, ", ") into a specific blank cell where you want to place the result, then drag the fill handle down to get all the corresponding values in one cell that you want, see screenshot:

Note: In the above formula, A2:A11 is the lookup range contains the lookup data, E2 is the lookup value, C2:C11 is the data range that you want to return the matching values from, "," is the separator to separate the multiple records.
Vlookup to return all matching values without duplicates into one cell

To ignore the duplicates in the returned matching values, please do with the below code.

1. Hold down the Alt + F11 keys to open the Microsoft Visual Basic for Applications window.

2. Click Insert > Module, and paste the following code in the Module Window.

VBA code: Vlookup and return multiple unique matched values into one cell

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. After inserting the code, then click Tools > References in the opened Microsoft Visual Basic for Applications window, and then, in the popped out References – VBAProject dialog box, check Microsoft Scripting Runtime option in the Available References list box, see screenshots:

4. Then click OK to close the dialog box, save and close the code window, return to the worksheet, and enter this formula: =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:

Note: In the above formula, A2:C11 is the data range you want to use, E2 is the lookup value, the number 3 is the column number which contains the returned values.

Vlookup to return multiple values into one cell with a useful feature

 If you have our Kutools for Excel, with its Advanced Combine Rows feature, you can quickly merge or combine the rows based on the same value and do some calculations as you need.

Note:To apply this Advanced Combine Rows, firstly, you should download the Kutools for Excel, and then apply the feature quickly and easily.

After installing Kutools for Excel, please do as follows:

1. Select the data range that you want to combine one column data based on another column.

2. Click Kutools > Merge & Split > Advanced Combine Rows, see screenshot:

3. In the popped out Advanced Combine Rows dialog box:

  • Click the key column name to be combined based on, and then click Primary Key
  • Then click another column that you want to combine its data based on the key column, and click Combine to choose one separator for separating the combined data.

4. Then click OK button, and you will get the following results:

Download and free trial Kutools for Excel Now !

More relative articles:

  • VLOOKUP Function With Some Basic And Advanced Examples
  • In Excel, the VLOOKUP function is a powerful function for most of Excel users, which is used to look for a value in the leftmost of the data range, and return a matching value in the same row from a column you specified. This tutorial is talking about how to use the VLOOKUP function with some basic and advanced examples in Excel.
  • Return multiple matching values based on one or multiple criteria
  • Normally, lookup a specific value and return the matching item is easy for most of us by using the VLOOKUP function. But, have you ever tried to return multiple matching values based on one or more criteria? In this article, I will introduce some formulas for solving this complex task in Excel.
  • Vlookup And Return Multiple Values Vertically
  • Normally, you can use the Vlookup function to get the first corresponding value, but, sometimes, you want to return all matching records based on a specific criterion. This article, I will talk about how to vlookup and return all matching values vertically, horizontally or into one single cell.
  • Vlookup And Return Multiple Values From Drop Down List
  • In Excel, how could you vlookup and return multiple corresponding values from a drop down list, which means when you choose one item from the drop down list, all of its relative values are displayed at once. This article, I will introduce the solution step by step.

The Best Office Productivity Tools

Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

  • Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
  • Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
  • Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
  • Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
  • Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
  • Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
  • Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
  • Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
  • More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial. 60-day money back guarantee.
kte tab 201905

Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
officetab bottom
Comments (43)
No ratings yet. Be the first to rate!
David Atwell
David Atwell
This comment was minimized by the moderator on the site
How would I adjust this formula to separate each returned value but ", " as well as only return unique values?
Reply
0
0
Michael
Michael     David Atwell
This comment was minimized by the moderator on the site
Thanks for the code!!

As for wildcards, a way around is using INSTR

You can replace the [ If rng = pValue Then ] with [ InStr(1, rng.Value, pValue) Then ] and if you don't want it to be case sensitive then use [ InStr(1, rng.Value, pValue, vbTextCompare) Then ]
Reply
0
0
David V
David V
This comment was minimized by the moderator on the site
Thanks for the VBA code above. Can you tell me how to make the results enter onto a new line in the cell, ie like Alt-Enter 300 400 1000 1300
Reply
0
0
Colleen
Colleen
This comment was minimized by the moderator on the site
Thank you for sharing the above code. I've been using this for several months now but today it doesn't seem to work. I'm getting blank cells instead of the usual error when there is data to be returned. Any thoughts?
Reply
0
0
Ajay
Ajay
This comment was minimized by the moderator on the site
Awesome work.. Got exactly what I want !!! Love it !!
Reply
0
0
Ankit Jain
Ankit Jain
This comment was minimized by the moderator on the site
Hi, i am really impressed with the work and its so easy to create one to use this function. however i need further support. My ? is that how can i select a number from a cell with multiple cell in my vlookup array. i.e. If cell A1 = 100, A2 = 350, A3 = 69 C1 = 100; 1222; 12133 C2 = 69; 222 D1 = Apple D2 = banana So how can select 100 from my table array column C to derive correspondent D1 = apple Please note that i have 7 digit numbers in my lookup value and table array which is separated by a ";". I would really appreciate if you can solve this and help me in saving a lot time.
Reply
0
0
Ari
Ari
This comment was minimized by the moderator on the site
Thank you for the VBA-code. I got exactly what I want! I modified only the code " rng.Offset(0, pIndex - 1) " to " rng.Offset(0, pIndex - 2) " . So is MYVLOOKUP able to search from Right to Left.
Reply
0
0
Jay
Jay
This comment was minimized by the moderator on the site
This is exactly what I was looking for and did not think of just making my own UDF. However it will not function exactly like VLOOKUP. If the string you are looking for is not only in the first column then it could give you data outside the original range passed. Name Number Other name Column not in range passed Jay 1 Jay 1 Jay 2 Jay 2 Chris 3 Chris 3 Jorge 4 Jorge 4 Jay 5 Jay 5 Jorge 6 Jorge 6 If the above table were cells A1:D7 if you passed only A1:C7 your "MYVLOOKUP" function returns 1 1 2 2 5 5 when you would expect it to return 1 2 5. The changes below fix the issue: Function MYVLOOKUP(pValue As String, pWorkRng As Range, pIndex As Long) 'Update 20150310 'Updated 6/9/16 Jay Coltrain 'Dim rng As Range Dim xResult As String xResult = "" Dim Rows As Long, i As Long Rows = pWorkRng.Rows.Count For i = 1 To Rows If pWorkRng.Cells(i, 1).Value = pValue Then xResult = xResult & " " & pWorkRng.Cells(i, 1).Offset(0, pIndex - 1) End If Next i 'For Each rng In pWorkRng ' If rng = pValue Then ' xResult = xResult & " " & rng.Offset(0, pIndex - 1) ' End If 'Next MYVLOOKUP = xResult End Function
Reply
0
0
cory
cory
This comment was minimized by the moderator on the site
This works great, but I need help with the command to remove duplicates from the results. Seriously though, Great work.
Reply
0
0
cory
cory
This comment was minimized by the moderator on the site
This works great, but I still need help with the command function to remove duplicates from the results.
Reply
0
0
Rob
Rob
This comment was minimized by the moderator on the site
Notify me of follow-up comments
Reply
0
0
Ajay
Ajay
This comment was minimized by the moderator on the site
Return nothing! after applying MYLOOKUP giving no result but blank.
Reply
0
0
Spyros Tzortzis
Spyros Tzortzis
This comment was minimized by the moderator on the site
Hi, It works well. What I would like to do is adapt the code to seperate thee value results with "///" or any other marker (for technical reasons, I don't want just a single character seperator). Also, I noticed that this formula doesnt work with a wild card. I know i am asking too much, but it doesnt as vlookup can work when i search for =myvlookup("*"&E6&"*",$A$2:$C$15,2) which it would do/could do. Any assistance?
Reply
0
0
Spyros Tzortzis
Spyros Tzortzis
This comment was minimized by the moderator on the site
Heads-up. I figured out how to get any separator in that output. Its rudimentary. But I figured it out. xResult = xResult & "///" & rng.Offset(0, pIndex - 1) The last and most wished for thing though is enabling it to work with wildcards in the search criteria. Thank you again for this beautiful and brilliant solution. Extremely helpful. Now just want to get get the macro to run and be installed in my excel perminantly no matter what I'm doing so I can use it when I need to. And wildcards! Thank you so much. Wildcards are all that are left to do.
Reply
0
0
Alex Chow
Alex Chow
This comment was minimized by the moderator on the site
To get unique record, you may use below: (modified by refer other user code) Function MYVLOOKUP(pValue As String, pWorkRng As Range, pIndex As Long) 'Update 20150310 'Updated 6/9/16 Jay Coltrain 'Dim rng As Range Dim xResult As String xResult = "" Dim Rows As Long, i As Long Rows = pWorkRng.Rows.Count For i = 1 To Rows If pWorkRng.Cells(i, 1).Value = pValue Then xResult = xResult & "," & pWorkRng.Cells(i, 1).Offset(0, pIndex - 1) End If Next i Dim varSection As Variant Dim sTemp As String Dim sDelimiter As String sDelimiter = "," For Each varSection In Split(xResult, sDelimiter) If InStr(1, sDelimiter & sTemp & sDelimiter, sDelimiter & varSection & sDelimiter, vbTextCompare) = 0 Then sTemp = sTemp & sDelimiter & varSection End If Next varSection MYVLOOKUP = Mid(sTemp, Len(sDelimiter) + 1) End Function
Reply
0
0
Curtis
Curtis
This comment was minimized by the moderator on the site
This worked perfectly, but it did take me some time to get the Function work properly within my 20 tab, 50k+ line spreadsheet. Now the BIG question is how to take that delimited string and then use each entry as an Index/Match (not married to Index/Match, but it seems faster) lookup value in another dataset, returning the SUM value of all returns into one cell. My scenario is that I have a Single Order that has multiple invoices. Your MYVLOOKUP Function works superbly to report back all of the invoices in one cell. What I want to do now is to take each concatenated return with the reported cell, spin through that array and total the amounts of payment of each invoice back into the formula cell. I appreciate any help that you can offer on this and thanks for the MYVLOOKUP Function!
Reply
0
0
Jay
Jay
This comment was minimized by the moderator on the site
No matter what I do, I always get #value! returned instead of a result. vlookup works just fine, so the data works. Already followed the process of enabling macros. I even combined everything into a single sheet. Any ideas??
Reply
0
0
Kabilankumar
Kabilankumar
This comment was minimized by the moderator on the site
Great macro, useful. But need to know can it be modified to check 2 criteria & does anyone found anyway to make wildcards work on it. Any help?
Reply
0
0
Will
Will
This comment was minimized by the moderator on the site
Is there a way of amending the result so instead of showing 1000 1000 -1000 it would show for example 1,000/1,000/(1,000) ?
Reply
0
0
Mark
Mark
This comment was minimized by the moderator on the site
Great function, however chunking through 100,000 records proves a bit much for my poor laptop, will need to let it run overnight!
Reply
0
0
Tom T
Tom T
This comment was minimized by the moderator on the site
This is amazing, thank you!
Reply
0
0
There are no comments posted here yet
Load More
Leave your comments
Guest
Posting as Guest
Login
×
Rate this post:
Reset
0   Characters
Suggested Locations