Lookup and return multiple values concatenated into one cell
This article demonstrates how to find a value in a column and concatenate corresponding values on the same row. The picture above shows an array formula in cell F3 that looks for value "Fruit" in column B and concatenates corresponding values in column C.
The TEXTJOIN function introduced in Excel 365 (subscription required) allows you to easily concatenate values. It also accepts arrays and nested functions.
However if your Excel version is missing the TEXTJOIN function you can use a User Defined Function, I have all instructions on how to do that in this post. The TEXTJOIN function is missing if you don't have the Office 365 subscription service.
Table of Contents
 Lookup and return multiple values concatenated into one cell [Array Formula Excel 365]
 Ignore duplicates [Array Formula Excel 365]
 Ignore duplicates [User Defined Function]
 Add a delimiting character between each value [UDF]
 Wildcard search [Array Formula]
 Wildcard search [User Defined Function]
 Searching for the first characters in a text string [Array Formula]
 Searching for the first characters in a text string [UDF]
 Lookup and return multiple dates concatenated into one cell [Array Formula]
 Lookup and return multiple dates concatenated into one cell [UDF]
 Lookup within a date range and return multiple values concatenated into one cell [Array Formula]
 Lookup within a date range and return multiple values concatenated into one cell [UDF]
 Split search string using a delimiting character and return multiple matching values concatenated into one cell
 Use multiple search values and return multiple matching values concatenated into one cell
Richard asks:
Looking for a formula that will take a part number from one column and go and look for all related vehicle applications per that part number and return the vehicle applications to a single cell related back to the part number.
Answer:
The array formula used in the image above in cell C11 is:
Here is another example:
Array formula in cell C2:
Watch a video where I explain the array formula
Recommended article
Concatenate unique distinct values
The new TEXTJOIN function in excel 2016 lets you do some amazing things with arrays. This post demonstrates how to [โฆ]
Concatenate unique distinct values
How to enter an array formula
Make sure you enter it as an array formula, follow this:
 Doubleclick cell C2
 Paste above formula to cell
 Press and hold CTRL + SHIFT simultaneously
 Press enter once
 Release all keys
If you did this the right way, the formula now has a beginning and ending curly bracket, like this:
{=TEXTJOIN(" ",TRUE,IF(A2='Vehicle applications'!$C$2:$C$13,'Vehicle applications'!$A$2:$A$13,""))}
Dont enter these characters yourself, they appear automatically if you follow the instructions above.
Then copy cell C2 and paste to cell range C3:C6.
Recommended article
Array formulas allows you to do advanced calculations not possible with regular formulas.
User Defined Function
UPDATE: The solution below is for excel versions that has the TEXTJOIN function missing.
I canยดt concatenate values with a formula but I can create a small user defined function.
User defined function in cell C2:
+ ENTER
Watch a video where I demonstrate the UDF
Picture of sheet "Vehicle applications"
Explaining user defined function
Lookup_concat(Look_up_value, Search_in_column, Concatenate_values_in_column)
Looks for a value in a column and returns a value on the same row from a column you specify. If multiple values are found the corresponding values are concatenated into a single cell.
See picture below.
VBA code
Function Lookup_concat(Search_string As String, _ Search_in_col As Range, Return_val_col As Range) Dim i As Long Dim result As String For i = 1 To Search_in_col.Count If Search_in_col.Cells(i, 1) = Search_string Then result = result & " " & Return_val_col.Cells(i, 1).Value End If Next Lookup_concat = Trim(result) End Function
How to add vba code to your workbook
 Press AltF11 to open visual basic editor
 Click Module on the Insert menu

Copy and paste code above to the code module
 Exit visual basic editor
 Save your workbook as a *.xlsm file
Ignore duplicates
The picture below shows you "odometer" found twice in the table but is only displayed once in cell C2. In other words, no duplicates allowed.
Array formula in cell C2:
Watch a video where I explain the formula
Recommended article
Use a drop down list to filter and concatenate unique distinct values
Question: Is there a way to have a unique list generated from a list? Meaning I have a sheet that [โฆ]
Use a drop down list to filter and concatenate unique distinct values
Explaining array formula in cell C2
Step 1  Look for a value in a column and return corresponding values
IF(A2='Vehicle applications'!$C$2:$C$13, 'Vehicle applications'!$A$2:$A$13, "")
becomes
=IF(1={1; 2; 3; 1; 2; 1; 2; 1; 5; 1; 2; 3}, {"speedometer"; "speedometer"; "speedometer"; "tachometer"; "tachometer"; "odometer"; "odometer"; "odometer"; "odometer"; "fuel gauge"; "fuel gauge"; "fuel gauge"}, "")
and returns
{"speedometer";"";"";"tachometer";"";"odometer";"";"odometer";"";"fuel gauge";"";""}
Step 2  Match cell range 'Vehicle applications'!A2:A13 with array from step 1
The array in step 1 has a duplicate value, to remove those we need to match the values to the list to find out which values are duplicates.
MATCH('Vehicle applications'!A2:A13, IF(A2='Vehicle applications'!$C$2:$C$13, 'Vehicle applications'!$A$2:$A$13, ""), 0)
becomes
MATCH({"speedometer"; "speedometer"; "speedometer"; "tachometer"; "tachometer"; "odometer"; "odometer"; "odometer"; "odometer"; "fuel gauge"; "fuel gauge"; "fuel gauge"}, {"speedometer"; ""; ""; "tachometer"; ""; "odometer"; ""; "odometer"; ""; "fuel gauge"; ""; ""}, 0)
and returns
{1;1;1;4;4;6;6;6;6;10;10;10}
Step 3  Check if array equals corresponding row number
It is certain that the value is a duplicate if they don't match.
IF(MATCH('Vehicle applications'!A2:A13, IF(A2='Vehicle applications'!$C$2:$C$13, 'Vehicle applications'!$A$2:$A$13, ""), 0)=MATCH(ROW('Vehicle applications'!$A$2:$A$13), ROW('Vehicle applications'!$A$2:$A$13)), 'Vehicle applications'!$A$2:$A$13, "")
becomes
IF({1;1;1;4;4;6;6;6;6;10;10;10}=MATCH(ROW('Vehicle applications'!$A$2:$A$13), ROW('Vehicle applications'!$A$2:$A$13)), 'Vehicle applications'!$A$2:$A$13, "")
becomes
IF({1;1;1;4;4;6;6;6;6;10;10;10}= {1;2;3;4;5;6;7;8;9;10;11;12}, 'Vehicle applications'!$A$2:$A$13, "")
becomes
IF({1; 1; 1; 4; 4; 6; 6; 6; 6; 10; 10; 10}= {1; 2; 3; 4; 5; 6; 7; 8; 9; 10; 11; 12}, {"speedometer"; "speedometer"; "speedometer"; "tachometer"; "tachometer"; "odometer"; "odometer"; "odometer"; "odometer"; "fuel gauge"; "fuel gauge"; "fuel gauge"}, "")
and returns
{"speedometer";"";"";"tachometer";"";"odometer";"";"";"";"fuel gauge";"";""}
Step 4  Concatenate array
TEXTJOIN(", ", TRUE, IF(MATCH('Vehicle applications'!A2:A13, IF(A2='Vehicle applications'!$C$2:$C$13, 'Vehicle applications'!$A$2:$A$13, ""), 0)=MATCH(ROW('Vehicle applications'!$A$2:$A$13), ROW('Vehicle applications'!$A$2:$A$13)), 'Vehicle applications'!$A$2:$A$13, ""))
becomes
=TEXTJOIN(", ", TRUE, {"speedometer";"";"";"tachometer";"";"odometer";"";"";"";"fuel gauge";"";""})
and returns
speedometer, tachometer, odometer, fuel gauge in cell C2.
User Defined Functions
If your excel version is missing the TEXTJOIN function you need to add two User Defined Functions to your workbook.
They let you return unique distinct values concatenated into one cell, one UDF creates a unique distinct list and the other UDF concatenates the values.
Watch a video where I explain how to use the UDF:
VBA code
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
How to add vba code to your workbook
Back to top
Recommended article
How to use the TEXTJOIN function
The TEXTJOIN function is a new function for office 365 subscribers, it is like the CONCATENATE function on steroids or what the CONCATENATE function [โฆ]
How to use the TEXTJOIN function
Delimiting character
The TEXTJOIN function lets you specify a delimiting character in the first argument.
This UDF lets you specify a delimiting character in the last argument.
=Lookup_concat(A2,'Vehicle applications'!$C$2:$C$13,'Vehicle applications'!$A$2:$A$13,"")
Watch a video where I explain the UDF
VBA Code
Function Lookup_concat(Search_string As String, _ Search_in_col As Range, Return_val_col As Range, str As String) Dim i As Long Dim result As String For i = 1 To Search_in_col.Count If Search_in_col.Cells(i, 1) = Search_string Then result = result & str & Return_val_col.Cells(i, 1).Value End If Next If Len(result) > 0 Then result = Right(result, Len(result)  Len(str)) Lookup_concat = Trim(result) Else Lookup_concat = "" End If End Function
How to add vba code to your workbook
Wildcard search
The way this works is that the formula in cell C2 checks if value in cell A2 matches a string in cell range C2:C13. In other words, the whole cell must not match but a substring must.
Number 1 is found in cell C2,C5 and C7.
Array formula in cell C2:
Watch a video where I explain the formula
Explaining array formula in cell C2
Step 1  Search for string in cell range
SEARCH(A2,'Vehicle applications'!$C$2:$C$13)
becomes
=SEARCH(1,{"A1";"A2";"A3";"A1";"A2";"A1";"A2";"A4";"A5";"A4";"A2";"A3"})
and returns
{2; #VALUE!; #VALUE!; 2; #VALUE!; 2; #VALUE!; #VALUE!; #VALUE!; #VALUE!; #VALUE!; #VALUE!}
Step 2  Return adjacent value
IF(SEARCH(A2,'Vehicle applications'!$C$2:$C$13),'Vehicle applications'!$A$2:$A$13,"")
becomes
IF({2; #VALUE!; #VALUE!; 2; #VALUE!; 2; #VALUE!; #VALUE!; #VALUE!; #VALUE!; #VALUE!; #VALUE!},'Vehicle applications'!$A$2:$A$13,"")
becomes
IF({2; #VALUE!; #VALUE!; 2; #VALUE!; 2; #VALUE!; #VALUE!; #VALUE!; #VALUE!; #VALUE!; #VALUE!},{"speedometer"; "speedometer"; "speedometer"; "tachometer"; "tachometer"; "odometer"; "odometer"; "odometer"; "odometer"; "fuel gauge"; "fuel gauge"; "fuel gauge"},"")
and returns
{"speedometer"; #VALUE!; #VALUE!; "tachometer"; #VALUE!; "odometer"; #VALUE!; #VALUE!; #VALUE!; #VALUE!; #VALUE!; #VALUE!}
Step 3  Remove errors
IFERROR(IF(SEARCH(A2,'Vehicle applications'!$C$2:$C$13),'Vehicle applications'!$A$2:$A$13,""),"")
becomes
IFERROR({"speedometer"; #VALUE!; #VALUE!; "tachometer"; #VALUE!; "odometer"; #VALUE!; #VALUE!; #VALUE!; #VALUE!; #VALUE!; #VALUE!},"")
and returns
{"speedometer";"";"";"tachometer";"";"odometer";"";"";"";"";"";""}
Step 4  Concatenate values
TEXTJOIN(",",TRUE,IFERROR(IF(SEARCH(A2,'Vehicle applications'!$C$2:$C$13),'Vehicle applications'!$A$2:$A$13,""),""))
becomes
TEXTJOIN(",",TRUE,{"speedometer";"";"";"tachometer";"";"odometer";"";"";"";"";"";""})
and returns "speedometer, tachometer, odometer" in cell C2.
User Defined Function
For those of you that have an earlier excel version. Watch this vide where I explain how to use it:
VBA Code
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 InStr(UCase(Search_in_col.Cells(i, 1)), UCase(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) For i = LBound(temp) To UBound(temp) result = result & " " & temp(i) Next i Lookup_concat = Trim(result) Else Lookup_concat = "" End If End Function
How to add vba code to your workbook
Searching for the first characters in a text string
The formula and UDF demonstrated below looks for a string that begins with the search string. If search string is a it will match ab, ac but not ba. ab and ac begins with a.
If search string is ab it will match abc, abz but not bca.
Array formula in cell C2:
Watch a video where I explain the formula
Explaining array formula in cell C2
Step 1  Crop strings so they match search string length
LEFT('Vehicle applications'!$C$2:$C$13, LEN(A2)
becomes
LEFT({"abc";"bvg";"cmm";"adf";"bvjt";"agf";"bvg";"dkl";"ehg";"aqw";"bvg";"cs"}, LEN("a")
and returns
{"a";"b";"c";"a";"b";"a";"b";"d";"e";"a";"b";"c"}
Step 2  Check if "a" is equal to values in array
IF(A2=LEFT('Vehicle applications'!$C$2:$C$13,LEN(A2)),'Vehicle applications'!$A$2:$A$13,"")
becomes
IF("a"={"a";"b";"c";"a";"b";"a";"b";"d";"e";"a";"b";"c"},'Vehicle applications'!$A$2:$A$13,"")
and returns
{"speedometer";"";"";"tachometer";"";"odometer";"";"";"";"fuel gauge";"";""}
Step 3  Concatenate values
TEXTJOIN(",", TRUE, IF(A2=LEFT('Vehicle applications'!$C$2:$C$13, LEN(A2)), 'Vehicle applications'!$A$2:$A$13, ""))
becomes
TEXTJOIN(",", TRUE, {"speedometer";"";"";"tachometer";"";"odometer";"";"";"";"fuel gauge";"";""})
and returns
"speedometer,tachometer,odometer, fuel gauge" in cell C2.
User defined function
If your excel version is missing TEXTJOIN function you can easily use the UDF below.
Function Lookup_concat(Search_string As String, _ Search_in_col As Range, Return_val_col As Range) Dim i As Long Dim result As String For i = 1 To Search_in_col.Count If Left(Search_in_col.Cells(i, 1), Len(Search_string)) = Search_string Then result = result & " " & Return_val_col.Cells(i, 1).Value End If Next Lookup_concat = Trim(result) End Function
How to add vba code to your workbook
Download Excel file
excelvbareturnmultipleanswersintoonecellstartingwithaspecificcharacterv2.xlsm
Lookup and return multiple dates concatenated into one cell
Excel sees dates as numbers so they can be used in formulas and calculations, Jan 1, 1900 is 1 and June 7, 2017 is 42893. To concatenate dates and not numbers we need to use the TEXT function to convert numbers to dates.
(Pic shows dates formatted in this order: YYYYMMDD)
Array formula in cell C2:
Watch a video where I explain the formula
Explaining formula in cell C2
Step 1  Convert datenumbers to dates
TEXT('Vehicle applications'!$A$2:$A$13,"YYYYMMDD")
becomes
TEXT({41642; 41738; 41657; 41708; 41709; 41653; 41692; 41656; 41717; 41674; 41675; 41664},"YYYYMMDD")
and returns
{"20140103"; "20140409"; "20140118"; "20140310"; "20140311"; "20140114"; "20140222"; "20140117"; "20140319"; "20140204"; "20140205"; "20140125"}
Step 2  Check if "a" is equal to values in array
IF(A2='Vehicle applications'!$C$2:$C$13,TEXT('Vehicle applications'!$A$2:$A$13,"YYYYMMDD"),"")
becomes
IF(A2='Vehicle applications'!$C$2:$C$13,{"20140103"; "20140409"; "20140118"; "20140310"; "20140311"; "20140114"; "20140222"; "20140117"; "20140319"; "20140204"; "20140205"; "20140125"},"")
becomes
=IF({TRUE;FALSE;FALSE;TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE},{"20140103"; "20140409"; "20140118"; "20140310"; "20140311"; "20140114"; "20140222"; "20140117"; "20140319"; "20140204"; "20140205"; "20140125"},"")
and returns
{"20140103";"";"";"20140310";"";"20140114";"";"";"";"20140204";"";""}
Step 3  Concatenate values
TEXTJOIN(", ",TRUE,IF(A2='Vehicle applications'!$C$2:$C$13,TEXT('Vehicle applications'!$A$2:$A$13,"YYYYMMDD"),""))
becomes
TEXTJOIN(", ",TRUE,{"20140103";"";"";"20140310";"";"20140114";"";"";"";"20140204";"";""})
and returns
"20140103, 20140310, 20140114, 20140204" in cell C2.
User defined function
If your excel version is missing TEXTJOIN function you can use this UDF.
Function Lookup_concat(Search_string As String, _ Search_in_col As Range, Return_val_col As Range) Dim i As Long Dim result As String For i = 1 To Search_in_col.Count If Search_in_col.Cells(i, 1) = Search_string Then result = result & ", " & Return_val_col.Cells(i, 1).Value End If Next Lookup_concat = Right(result, Len(result)  2) End Function
How to add vba code to your workbook
Lookup within a date range and return multiple values concatenated into one cell
The following formula and UDF use a start and end date to filter dates in col C (table2) and return corresponding items in col A.
Array formula in cell D3 (table1):
Watch a video where I explain the formula
Explaining formula in cell D3
Step 1  Build logical expression to filter dates in col C (table 2) using start and end dates in table 1
(A3<='Vehicle applications'!$C$2:$C$13) *(B3>='Vehicle applications'!$C$2:$C$13)
becomes
(42788<={42791; 42794; 42798; 42742; 42776; 42791; 42751; 42815; 42798; 42788; 42807; 42819}) *(42791>={42791; 42794; 42798; 42742; 42776; 42791; 42751; 42815; 42798; 42788; 42807; 42819})
and returns
{1;0;0;0;0;1;0;0;0;1;0;0}
Step 2  Return value on same row as matching date
IF((A3<='Vehicle applications'!$C$2:$C$13) *(B3>='Vehicle applications'!$C$2:$C$13), 'Vehicle applications'!$A$2:$A$13, "")
becomes
=IF({1;0;0;0;0;1;0;0;0;1;0;0}, {"S"; "P"; "E"; "S"; "O"; "N"; "R"; "P"; "Q"; "F"; "X"; "V"}, "")
and returns
{"S";"";"";"";"";"N";"";"";"";"F";"";""}
Step 3  Concatenate values
TEXTJOIN(", ", TRUE, IF((A3<='Vehicle applications'!$C$2:$C$13) *(B3>='Vehicle applications'!$C$2:$C$13), 'Vehicle applications'!$A$2:$A$13, ""))
becomes
TEXTJOIN(", ", TRUE, {"S";"";"";"";"";"N";"";"";"";"F";"";""})
and returns
"S, N, F" in cell D3.
User Defined Function
If your excel version is missing TEXTJOIN function you can use this UDF.
Function Lookup_concat(Search_Start As String, Search_End As String, _ Search_in_col As Range, Return_val_col As Range) Dim i As Long Dim result As String For i = 1 To Search_in_col.Count If Search_in_col.Cells(i, 1) <= Search_End And Search_in_col.Cells(i, 1) >= Search_Start Then result = result & Return_val_col.Cells(i, 1).Value & ", " End If Next If Len(result) > 0 Then Lookup_concat = Left(result, Len(result)  2) Else Lookup_concat = "" End If End Function
How to add vba code to your workbook
Split search string using a delimiting character and return multiple matching values concatenated into one cell
This UDF lets you use multiple search strings and fetch corresponding values concatenated to one cell.
Example, search string in cell A2 (table 1) is A+B, the search delimiting character is +. The UDF looks for both A and B in column A in table 2. Cell A2 and A3 matches A and B so the values from column B (Biology and Chemistry) are retrieved and concatenated to cell B2.
Formula in cell B2:
This is a User Defined Function so to use the above formula you need to first insert a few lines of code to your workbook.
User Defined Function
Function Lookup_concat(Search_string As String, Search_del As String, _ Search_in_col As Range, Concat_del As String, Return_val_col As Range) Dim i As Long, j As Long Dim result As String Dim srchArr() As String srchArr = Split(Search_string, Search_del) For i = 1 To Search_in_col.Count For j = LBound(srchArr) To UBound(srchArr) If Search_in_col.Cells(i, 1) = srchArr(j) Then result = result & Return_val_col.Cells(i, 1).Value & Concat_del End If Next j Next Lookup_concat = Left(result, Len(result)  Len(Concat_del)) End Function
How to add vba code to your workbook
Remember to save your workbook as an *.xlsm file or your code is lost the next time you open your workbook.
Download Excel file
plitsearchstringusingadelimitingcharacterandreturnmultiplematchingvaluesconcatenatedintoonecell.xlsm
Use multiple search values and return multiple matching values concatenated into one cell
Formula in cell G3:
VBA Code
Function Lookup_concat(Search_string As Range, Search_in_col As Range, Concat_del As String, Return_val_col As Range) Dim i As Long, j As Long Dim result As String For i = 1 To Search_in_col.Count For Each Value In Search_string If Search_in_col.Cells(i, 1) = Value Then result = result & Return_val_col.Cells(i, 1).Value & Concat_del End If Next Value Next i Lookup_concat = Left(result, Len(result)  Len(Concat_del)) End Function
Download Excel *.xlsm file
Use multiple search values and return multiple matching values concatenated into one cell.xlsm
Use a drop down list to filter and concatenate unique distinct values
Question: Is there a way to have a unique list generated from a list? Meaning I have a sheet that [โฆ]
Concatenate cell values based on a condition [No VBA]
Add cell values to a single cell with a condition, no VBA in this article.
Joining multiple cell values in Excel is not easy, for example, the CONCATENATE function allows you to only reference a [โฆ]
How to use the TEXTJOIN function
The TEXTJOIN function is a new function for office 365 subscribers, it is like the CONCATENATE function on steroids or what the CONCATENATE function [โฆ]
Concatenate unique distinct values
The new TEXTJOIN function in excel 2016 lets you do some amazing things with arrays. This post demonstrates how to [โฆ]
Is there a way to list speedometer tachometer, etc... on separate lines in the same cell instead of separating them with a space? Similar to hitting the ALT+ENTER to create two lines of info in one cell like this:
cell C2
speedometer
tachometer
Tom,
Sure!
VBA code:
Function Lookup_concat(Search_string As String, _
Search_in_col As Range, Return_val_col As Range)
Dim i As Long
Dim result As String
For i = 1 To Search_in_col.Count
If Search_in_col.Cells(i, 1) = Search_string Then
result = result & " " & Return_val_col.Cells(i, 1).Value & Chr(10)
End If
Next
Lookup_concat = Trim(result)
End Function
Instructions:
Select cells.
Press CTRL + 1.
Click tab "Alignment"
Enable "Wrap text"
Click ok!
Oscar,
I just left you a similar comment on another page. Is there a way to do this and put the results into new rows instead of the same cell?
1 Capacitor 1
2 Capacitor 2
Would become:
1 Capacitor 1 Speedometer
1 Capacitor 1 tachometer
1 Capacitor 1 odometer
1 Capacitor 1 fuel gauge
2 Capacitor 2 Speedometer
2 Capacitor 2 tachometer
... and so on
Thanks!
Peter
Hi Oscar,
Your macros is awesome!
A quick question though, if I need to search and return values in rows instead of columns, how can I edit the macro to reflect this? My interim fix was to transpose the entire table.
Thanks!
Cheers,
Faus
Oscar thank for the quick response. Is there a way to do this without UDF?
What I am trying to do is make an output matrix which has various wire types listed in column B4 thru B23 and terminating connector types listed across row C4 to AA4. I am trying to populate basically all of my open cells between C4 and AA23 with actual six digit part numbers. In some cases there are two six digit part numbers that must show up in the same cell. The data is being looked up in another tab in the spreadsheet with wire type in column C, part numbers in column B and connector type in column E. I also have a column called Standard flagged with a 1, 0 or black. What I am currently doing is using a combination of Index and Match in my output matrix that looks up a part number based on three criteria, wire type, connector type and a standard part (flagged with 1). If a row meets those three criteria the part number is grabbed and filled into the output matrix. I have that part working, but don't know how to list two part number in the same cell if that condition exists.
Is there a better way of doing this task?
Tom,
You could try to concatenate two formulas in a cell. See this page: https://excel.tips.net/Pages/T002788_Simulating_AltEnter_in_a_Formula.html
Thanks Oscar. Does the UDF stay with the file (embedded) if it is emailed and shared around the office or do you have to setup the VBA code on each individual's machine?
Tom,
The udf stays with the *.xls file.
Oscar thanks for this one. Saved me lot of time
Gonzo,
you are very welcome!
Oscar, I love this and am planning to use the code for a stock checking application. My Question is can it be modified to put the results on separate colums [in line with each search row]
Search Item Item1 Item2 Items 3
Ray, yes it can. But it would be easier to use this formula: How to return multiple values using vlookup in excel with a minor change.
How to create an array formula
Copy (Ctrl + c) and paste (Ctrl + v) array formula into formula bar.
Press and hold Ctrl + Shift.
Press Enter once.
Release all keys.
How to copy array formula
Select cell
Copy (Ctrl + c)
Select cell or cells to the right
Paste (Ctrl + v)
Hi Oscar
I want to use your above formula with a calendar in excel but I keep getting a #VALUE! error.
I have a data sheet that has data simular to this
1/2/2011 Red
1/3/2011 Blue
1/3/2011 Green
1/5/2011 Purple
I copied the lookup_concat udf and my function in column F5 of my calendar is...
=Lookup_concat(F4, Data!A4:A147, Data!B4:B147)
F4 is the field that has my date I was to search for.
Julie,
I am guessing the dates causes problems.
1. Select F4
2. Press Ctrl + 1
3. Click Category: General
4. Remember the value
5. Click Cancel
Select the same date in range Data!A4:A147.
Repeat step 2 to 5.
Are the values the same?
How Excel Stores Dates And Times
Yes they are the same. I does work with vlookup but I want to be able to return multiple values.
This is my vlookup function in another cell and it does work. =VLOOKUP(D4,Data!A4:B378,2,FALSE)
Julie,
I have no idea!
Send me a workbook without sensitive data and Iยดll see what I can do.
Wow Oscar, you rule! Searched the internet and beyond looking for this tiny fuction, thank you very much!
Bernard,
Thanks!
Hi Oscar  nice code you've got up here, and great explanation. This is close to what I need, but I'm trying to take multiple input values and output to one cell. I'm doing a skills inventory, where someone could have multiple skills like say Excel and Powerpoint. I'd like to be able to enter multiple numbers and return multiple values  got any tricks for that? Thanks!
Michael,
Yes, open attached file: excelvbasearchformultiplevaluesandreturnmultiplevaluesintoonecell.xls
Hi Oscar, your site had been a phenomenal help so far to me. I am interested in expanding the search multiple values/return multiple values to include multiple criteria
Would be entered something like this:
Lookup_Concat(Return_Col, Search_Col1, Range1, Search_Col2, Range2, Search Col 3, Range3)
What change(s) to the multiple value/multiple return would I make? Thanks!  A
Oscar  you make this look entirely too easy. I also came up with a way (with some help from a friend) where we changed the lookup key to text and used some string functions to get the desired result w/out VBA. This is more elegant, however. If you like I can post/send the file, I'm just not sure how to do that from this page. Thanks again!  Mike
Downloaded example, but showed #NAME? error in column C under Related vehicle applications.
Looks like an error in the code?
I had the same problem. It turned out that I didnt have macros enabled in the trust center
a,
I downloaded the file and it works here.
Is there anyway to get it to ignore duplicates entries?
so that each 'vehicle application' is listed once in the 'related vehicle applications' no matter how many times it appears in the 'vehicle application' list?
Matt,
Yes, download example file:
excelvbareturnmultipleuniquevaluesintoonecell.xls
How would you integrate the multiple criteria lookup_concat function with the above mentioned functionality to remove duplicates?
Thank you Oscar
that has proved to be very useful
Hi Oscar thank you for your code it works well for me with a small exception. I have used your code shown to Tom (26th Jan 2011). When the code concatenates two or more text strings the text is placed on successive lines but there is always a ALT + ENTER character at the end of the text that adds an extra line below the last text string.
Is there any way to prevent the this last ALT + ENTER from being added?
Thank you for your help
Chris,
Search_in_col As Range, Return_val_col As Range)
Dim i As Long
Dim result As String
For i = 1 To Search_in_col.Count
If Search_in_col.Cells(i, 1) = Search_string Then
result = result & " " & Return_val_col.Cells(i, 1).Value & Chr(10)
End If
Next
result = Left(result, Len(result)1)
Lookup_concat = Trim(result)
End Function
Oscar, Very Nifty, Thanks
Modified to insert commas & ampersand:
Function Lookup_concat(Search_string As String, _
Search_in_col As Range, Return_val_col As Range)
Dim i As Long
Dim n As Integer
Dim c As Integer
Dim result As String
For i = 1 To Search_in_col.Count
If Search_in_col.Cells(i, 1) = Search_string Then
n = n + 1
End If
Next
For i = 1 To Search_in_col.Count
If Search_in_col.Cells(i, 1) = Search_string Then
c = c + 1
Select Case c
Case 1
result = Return_val_col.Cells(i, 1).Value
Case n
result = result & " & " & _Return_val_col.Cells(i, 1).Value
Case Else
result = result & ", " & Return_val_col.Cells(i, 1).Value
End Select
End If
Next
Lookup_concat = Trim(result)
End Function
This is great. How do I make this UDF available for all my spreadsheets without having to inserting it into each spreadsheet?
This is so wonderful. Is there a way to add commas between each entry?
Vicki,
Hi, this works great with the comma though is it possible to not start the result with a comma as it appears to do but end with a comma in the result?
Was their ever a response to this? Is there a way to not start off with a comma but have any entries after the first be separated with a comma?
Hi Holly,
Try this.
Thanks,
Alex
_____________________________________________
Function Lookup_concat(Search_string As String, _
Search_in_col As Range, Return_val_col As Range)
Dim i As Long
Dim result As String
For i = 1 To Search_in_col.Count
If Search_in_col.Cells(i, 1) = Search_string Then
result = result & ", " & Return_val_col.Cells(i, 1).Value
End If
Next
result = Mid(result, 2, Len(Trim(result))  1)
Lookup_concat = Trim(result)
End Function
Alex,
That worked perfectly!! Thank you so much for your help!
Holly
Is there anyway to get this to work by adding in a third factor? I have a similar list with part numbers, but I only want to concatenate the products (or stores in my case) that have prices.
Rich,
Excel toolbox: Save your custom functions and macros in an AddIn
Thanks Oscar, I keep getting #VALUE! I believe this is happening because some of the search fields have #NAME errors. Any way to modify the script to ignore errors and continue looking?
Hi Oscar,
You are unbelievable with this! I have a question....
What if the return values from the function (Return_val_col) were integers and instead of listing all of the separate values in one cell, the function returned the sum of the values...
do you know how I could do that using the function that you created?
Naajia,
I attached both a vba and a formula solution. I do recommend using the formula.
https://www.getdigitalhelp.com/wpcontent/uploads/2010/12/Naajia.xls
Oscar  found this with a Google search, and it is EXACTLY what I needed. THANK YOU for your contributions here!!! Saved me a TON of time on a vital project.
Thank you Oscar.
Found this the same as Brad with Google search.
This very nice and clearly illustrated example helped me quite a bit.
Hello,
I was wondering if you could help me change the column lookup to a row based lookup. Instead of searching in one column I would like to search in 1 row for a certain number. Then display the results just as you have done.
Thank you.
Katherine
Oscar,
Like the rest, I'm thrilled to find your formula. Could it be modified to have a second (and maybe third) search column, kind of like a COUNTIFS() function would do?
Thanks,
Peter
Peter,
Did you figure out a way to set this up with multiple criteria?
Where I've put 17, just include the number of columns further away.
Option Explicit
Function Lookup_concat(Search_string As String, _
Search_in_col As Range, Return_val_col As Range)
Dim i As Long
Dim result As String
For i = 1 To Search_in_col.Count
If Search_in_col.Cells(i, 1) = Search_string Then
result = result & ", " & Return_val_col.Cells(i, 1).Value & " " & Return_val_col.Cells(i, 17).Value
End If
Next
Lookup_concat = Mid(result, 3)
End Function
Here is a link to an article I wrote in a "mini" blog I host which contains a UDF that I derived from this blog article's code but to which I added several additional optional arguments that provide some useful (I hope) flexibility when performing your lookup...
https://www.excelfox.com/forum/f22/lookupvalueconcatenateallfoundresults345/
Katherine,
Yes, see attached example file!
excelvbareturnmultipleanswersintoonecellhorizontallookup.xls
Rick Rothstein (MVP  Excel),
I tried your udf and all the optional arguments. It works great, I am sure it will be useful!
Hi Oscar,
Thanks very much for this UDF. Very useful. Is there a way that you know it can be used in a data validation list? I get an error if I use it and I read somewhere else that it's not possible to use UDF's. Basically I have a large table with fields 'country', 'operator', 'plan'. In another table I want to select a country, then in a second column get a list of (unique) operators available in that country and select one, and in a third column then select a plan based on the country operator choice in the other columns. Your UDF (with the appropriate separator and a little tweaking perhaps) would be ideal for that, but I need to find a way to use it in selection lists.
Thanks,
Mario.
Mario Hoek,
I think you will find these posts interesting:
https://www.getdigitalhelp.com/2010/07/17/createdependentdropdownlistscontaininguniquedistinctvaluesinexcel/
https://www.getdigitalhelp.com/2012/02/22/dependentdatavalidationlistsinmultiplerows/