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

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]Column1[/td][td][/td][td=bgcolor:#5B9BD5]Column1[/td][td=bgcolor:#5B9BD5]Column2[/td][td][/td][td=bgcolor:#70AD47]Column1[/td][td=bgcolor:#70AD47]Column2[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]357 359 431 452 576 589 106[/td][td][/td][td=bgcolor:#DDEBF7]351 353 355 357[/td][td=bgcolor:#DDEBF7]mario[/td][td][/td][td=bgcolor:#E2EFDA]357[/td][td=bgcolor:#E2EFDA]mario[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td]431 433 435 437[/td][td]ivan[/td][td][/td][td]431[/td][td]ivan[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td=bgcolor:#DDEBF7]100 102 104 106[/td][td=bgcolor:#DDEBF7]paul[/td][td][/td][td=bgcolor:#E2EFDA]106[/td][td=bgcolor:#E2EFDA]paul[/td][/tr]
[/table]

example excel file

after change any value use Ctrl+Shift+F5

 

[Whatri]

Thank you Eric, woked like a charm!

 

[Eric W]

You're welcome!