Chrisa asks:

Hi Oscar...this is a very interesting function and helped me a lot so far.
My file though is a bit more complicated..

I have multiple info in one cell separated with ";" (example AD1; AD2; AD3) lets say that these are servers (File name SERVERS) and in each server I have multiple applications.

I have now another file that has all the applications per server per line in excel (each line has one server one application. File name: APPS).

I want starting from the file SERVERS to look up the servers that are in one cell find them in the second file APPS and bring all the applications also in one cell in the file SERVERS.

Any ideas here?
Thanks in advance
C

Answer:

Sheet 2

Lookup multiple values in on cell

Sheet 1

Lookup multiple values in on cell1

Formula in cell B2:

=Lookup_concat(A2, Sheet2!$A$2:$A$10, Sheet2!$B$2:$B$10)

VBA code

Function Lookup_concat(Search_string As String, _
  Search_in_col As Range, Return_val_col As Range)
Dim i As Long, result As String
Dim Search_strings, Value As Variant
Search_strings = Split(Search_string, ";")
For Each Value In Search_strings
    For i = 1 To Search_in_col.Count
      If Search_in_col.Cells(i, 1) = Value Then
        result = result & " " & Return_val_col.Cells(i, 1).Value
      End If
    Next i
Next Value
Lookup_concat = Trim(result)
End Function

What now?

You can use the code (see instructions below) in your workbook or download the example file.

  1. Copy vba code
  2. Open VB Editor (Alt+F11)
  3. Click Insert
  4. Click Module
  5. Paste code to module1
  6. Exit VB Editor

Lookup multiple values in on cell2

 

Download excel *.xlsm file

Lookup multiple values in one cell.xlsm