Vlookup multiple values for multiple results

[Whatri]

Hi everyone,

I have a specific issue i cannot find my way around it..
In cell A1 i have numbers:
357 359 431 452 576 589

In the other sheet i have numbers and names in 2 columns, numbers in A, names in B
351 353 355 357 mario
431 433 435 437 ivan
100 102 104 106 paul

Which formula can i use to search through every number on my first sheet, and to retrieve multiple names with the matching numbers from other sheet?

 

[Eric W]

Welcome to the board.

This can be done with native Excel formulas, but they'd be long, complicated, and require multiple help columns. You'd probably be better off with a UDF (User-Defined Function). If you want to try that:

Open a copy of your workbook. Press Alt-F11 to open the VBA editor. From the menu, click Insert > Module. In the sheet that opens, paste this code:

Public Function GetNames(ByVal Src As Range, Tbl As Range) As String
Dim Nums As Variant, MyTab As Variant, i As Long, j As Long

    GetNames = ""
    Nums = Split(Src)
    MyTab = Tbl.Value
    For i = 0 To UBound(Nums)
        For j = 1 To UBound(MyTab)
            If InStr(" " & MyTab(j, 1) & " ", " " & Nums(i) & " ") > 0 Then
                GetNames = GetNames & ", " & MyTab(j, 2)
            End If
        Next j
    Next i
    GetNames = Mid(GetNames, 3)
End Function

Press Alt-Q to close the VBA editor. In B1 of Sheet1, enter this formula:

=GetNames(A1,Sheet2!$A$1:$B$10)

Change the table range as needed. Drag down the column if needed. Let us know if this works for you.

 

[sandy666]

how about PowerQuery

Column1		Column1	Column2		Column1	Column2
357 359 431 452 576 589 106		351 353 355 357	mario		357	mario
		431 433 435 437	ivan		431	ivan
		100 102 104 106	paul		106	paul

example excel file

after change any value use Ctrl+Shift+F5

 

[Whatri]

Thank you Eric, woked like a charm!

 

[Eric W]

You're welcome!