What formula do I use?

[atravalgia]

Hello,

I have been trying all morning to play around with formulas and get this to work but I have had no luck. In the attached sheet, in the "rankings" tab I am looking to look up the name in column A, find it in the "sheet4" tab, and have it return all the results to the right of the name in the tab starting with cell "B2" in the "rankings" tab.

I have tried using vlookup and arrayformula and have had no luck. Any help would be appreciated!

[#2]

@atravalgia 

So I have to say, I was expecting there to be multiple teams and positions associated with the names, and so laid it out so that the results of the FILTER function would be able to "spill" down to multiple rows. So far, and I've not gone through more than a few, each has but one that comes up.

VLOOKUP may not have been working because the names in Sheet4 were not in alphabetical order. If you re-sort it then it would work. I did so in the attached, after trying the other approach. It worked.