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


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)


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

Download excel example file

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