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 2016 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.

Table of Contents

  1. Lookup and return multiple values concatenated into one cell
    1. Array formula [Excel 2016]
    2. User Defined Function
      1. Download workbook
  2. Ignore duplicates
    1. Array Formula [Excel 2016]
      1. Explaining array formula
    2. User Defined Function
      1. Download workbook
  3. Add a delimiting character between each value
    1. User Defined function
      1. Download workbook
  4. Wildcard search
    1. Array formula
      1. Explaining array formula
    2. User Defined Function
      1. Download workbook
  5. Searching for the first characters in a text string
    1. Array formula
      1. Explaining array formula
    2. User Defined Function
      1. Download Excel file
  6. Lookup and return multiple dates concatenated into one cell
    1. Array Formula
      1. Explaining array formula
    2. User Defined Function
      1. Download workbook
  7. Lookup within a date range and return multiple values concatenated into one cell
    1. Array Formula
      1. Explaining array formula
    2. User Defined Function
      1. Download workbook
  8. Split search string using a delimiting character and return multiple matching values concatenated into one cell
    1. User Defined Function
      1. Download workbook

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:

Array formula in cell C2:

=TEXTJOIN(" ",TRUE,IF(A2='Vehicle applications'!$C$2:$C$13,'Vehicle applications'!$A$2:$A$13,""))

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 […]

Comments(2) Filed in category: Concatenate, Excel, Textjoin

How to enter an array formula

Make sure you enter it as an array formula, follow this:

  1. Doubleclick cell C2
  2. Paste above formula to cell
  3. Press and hold CTRL + SHIFT simultaneously
  4. Press enter once
  5. 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

Learn the basics of Excel arrays

Array formulas allows you to do advanced calculations not possible with regular formulas.

Comments(2) Filed in category: Count values, Excel

Back to top

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:

=Lookup_concat(A2, 'Vehicle applications'!$C$2:$C$13, 'Vehicle applications'!$A$2:$A$13)

+ 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

Back to top

How to add vba code to your workbook

  1. Press Alt-F11 to open visual basic editor
  2. Click Module on the Insert menu
  3. Copy and paste code above to the code module
    code-module
  4. Exit visual basic editor
  5. Save your workbook as a *.xlsm file

Back to top

Download excel *.xls file

excel vba - return multiple answers into one cell.xls
(Excel 97-2003  Workbook *.xls)

Back to top

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:

=TEXTJOIN(", ", TRUE, IF(IFERROR(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, ""))

Watch a video where I explain the formula

Recommended article

Use a drop down list to extract 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 […]

Comments(6) Filed in category: Concatenate, Drop down lists, Excel

How to enter an array formula

Back to top

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.

Back to top

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

Back to top

How to add vba code to your workbook

Download excel *.xls file

lookup and return multiple concatenated unique distinct values.xlsm

Back to top

Recommended article

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 […]

Comments(4) Filed in category: Excel, Textjoin

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

Download excel *.xlsm file

lookup and return multiple concatenated values - delimiting character.xlsm

Back to top

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 sub-string must.

Number 1 is found in cell C2,C5 and C7.

Array formula in cell C2:

=TEXTJOIN(",",TRUE,IFERROR(IF(SEARCH(A2,'Vehicle applications'!$C$2:$C$13),'Vehicle applications'!$A$2:$A$13,""),""))

Watch a video where I explain the formula

How to enter an array formula

Back to top

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.

Back to top

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

Back to top

Download *.xlsm file

excel-vba-return-multiple-unique-values-into-one-cell-wildcard-searchv2.xlsm

Back to top

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:

=TEXTJOIN(",", TRUE, IF(A2=LEFT('Vehicle applications'!$C$2:$C$13, LEN(A2)), 'Vehicle applications'!$A$2:$A$13, ""))

Watch a video where I explain the formula

How to enter an array formula

Back to top

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.

Back to top

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

Back to top

Download *.xlsm file

excel-vba-return-multiple-answers-into-one-cell-starting-with-a-specific-characterv2.xlsm

Back to top

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: YYYY-MM-DD)

Array formula in cell C2:

=TEXTJOIN(", ",TRUE,IF(A2='Vehicle applications'!$C$2:$C$13,TEXT('Vehicle applications'!$A$2:$A$13,"YYYY-MM-DD"),""))

Watch a video where I explain the formula

Back to top

Explaining formula in cell C2

Step 1 - Convert datenumbers to dates

TEXT('Vehicle applications'!$A$2:$A$13,"YYYY-MM-DD")

becomes

TEXT({41642; 41738; 41657; 41708; 41709; 41653; 41692; 41656; 41717; 41674; 41675; 41664},"YYYY-MM-DD")

and returns

{"2014-01-03"; "2014-04-09"; "2014-01-18"; "2014-03-10"; "2014-03-11"; "2014-01-14"; "2014-02-22"; "2014-01-17"; "2014-03-19"; "2014-02-04"; "2014-02-05"; "2014-01-25"}

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,"YYYY-MM-DD"),"")

becomes

IF(A2='Vehicle applications'!$C$2:$C$13,{"2014-01-03"; "2014-04-09"; "2014-01-18"; "2014-03-10"; "2014-03-11"; "2014-01-14"; "2014-02-22"; "2014-01-17"; "2014-03-19"; "2014-02-04"; "2014-02-05"; "2014-01-25"},"")

becomes

=IF({TRUE;FALSE;FALSE;TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE},{"2014-01-03"; "2014-04-09"; "2014-01-18"; "2014-03-10"; "2014-03-11"; "2014-01-14"; "2014-02-22"; "2014-01-17"; "2014-03-19"; "2014-02-04"; "2014-02-05"; "2014-01-25"},"")

and returns

{"2014-01-03";"";"";"2014-03-10";"";"2014-01-14";"";"";"";"2014-02-04";"";""}

Step 3 - Concatenate values

TEXTJOIN(", ",TRUE,IF(A2='Vehicle applications'!$C$2:$C$13,TEXT('Vehicle applications'!$A$2:$A$13,"YYYY-MM-DD"),""))

becomes

TEXTJOIN(", ",TRUE,{"2014-01-03";"";"";"2014-03-10";"";"2014-01-14";"";"";"";"2014-02-04";"";""})

and returns

"2014-01-03, 2014-03-10, 2014-01-14, 2014-02-04" in cell C2.

Back to top

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

Back to top

Download *.xlsm file

excel-vba-return-multiple-dates-into-one-cell.xlsm

Back to top

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):

=TEXTJOIN(", ", TRUE, IF((A3<='Vehicle applications'!$C$2:$C$13) *(B3>='Vehicle applications'!$C$2:$C$13), 'Vehicle applications'!$A$2:$A$13, ""))

Watch a video where I explain the formula

How to enter an array formula

Back to top

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.

Back to top

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

Back to top

Download *.xlsm file

lookup within a date range and return concatenated values.xlsm

Back to top

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:

=Lookup_concat(A3,"+",Sheet2!$A$2:$A$6,", ",Sheet2!$B$2:$B$6)

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.

Back to top

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.

Back to top

Download *.xlsm file

Split search string using a delimiting character and return multiple matching values concatenated into one cell.xlsm

Back to top