Anil asks:

I have
A1(anil singh raj)
It can be anything Like
A1(singh raj anil)
I want return value in
B1 (10 30 20)
B1(30 20 10)
Lookup array is
D1(anil) E1(10)
D2(raj) E2(20)
D3(singh) E3(30)

I have made a small custom function to split the search string and get the values you are looking for.

Formula in cell B1:


User defined function

Function SearchValues(str As Range, search_col As Range, return_col As Range)
Dim j As Long, i As Long
arr = Split(str, " ")
j = search_col.Rows.CountLarge
For Each Vl In arr
    For i = 1 To j
        If search_col.Cells(i, 1) = Vl Then result = result & return_col.Cells(i, 1) & " "
    Next i
Next Vl
SearchValues = result
End Function

How do I use the code above in my workbook?

You need to copy the code above and paste it to a code module, detailed instructions below.

  1. Open vb editor (shortcut keys: Alt+F11)
  2. Insert a new module
  3. Paste code to code module
  4. Exit vb Editor

Download excel *.xlsm file

Split search value using delimiter and search for each string.xlsm