Table of Contents

  1. Lookup and return multiple values concatenated into one cell
  2. Lookup and return multiple values concatenated into one cell - ignore duplicates
  3. Lookup and return multiple values concatenated into one cell - add a comma between each value
  4. Lookup and return multiple values concatenated into one cell - Wildcard search and ignore duplicates
  5. Lookup and return multiple values concatenated into one cell - Searching for the first character in a text string
  6. Lookup and return multiple dates 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:

I can´t do that with formula but I can create a 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

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 then returns values in the same rows from a column you specify, 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 implement user defined function in excel

  1. Press Alt-F11 to open visual basic editor
  2. Click Module on the Insert menu
  3. Copy and paste the above user defined function
  4. Exit visual basic editor

Recommended reading

Download excel example file

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