Table of Contents
- Lookup and return multiple values concatenated into one cell
- Lookup and return multiple values concatenated into one cell - ignore duplicates
- Lookup and return multiple values concatenated into one cell - add a comma between each value
- Lookup and return multiple values concatenated into one cell - Wildcard search and ignore duplicates
- Lookup and return multiple values concatenated into one cell - Searching for the first character in a text string
- Lookup and return multiple dates concatenated into one cell
- Split search string using a delimiting character and return multiple matching values concatenated into one cell
- Lookup for a partial match and return multiple values concatenated into one cell
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:
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.
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 Alt-F11 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
- Filter unique words from a range in excel (udf)
- Count the number of cells within a range that match multiple comma separated values
- Excel udf: Filter common values between two cell ranges in excel
Download excel example file
excel vba - return multiple answers into one cell.xls
(Excel 97-2003 Workbook *.xls)