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
  7. Split search string using a delimiting character and return multiple matching values concatenated into one cell
  8. Lookup for a partial match and return multiple 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:

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

Recommended reading

Download excel example file

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