return multiple similar data on multi variable search

The best way I can describe this is to imagine a large database sorted on a persons name but the name appears many times in the database under different years, different jobs etc. Lets take an example of Lahmans baseball database or baseball reference just to visualize. You have a player who has played many years on many different teams even multiple teams in the same year. He has also played multiple positions on those multiple teams. Lets now say we want to see his fielding stats for a particular year broken down by position or his batting stats for the year no matter on how many teams he has played. Can this be done?

I'd try two different things.

1) Sort & Filter

2) Pivot table

table is already sorted...how do I get more than just the first 'hit' on the name to come into the worksheet. I have MANY names and I don't really want to sort on each one individually. I want to bring in to a worksheet let's say all the occurrences of that name in one year then the next name etc where name 1 might first occur in say record 4500 but name 2 doesn't show up for the first time until record 45000.

or to put it more simply I guess...How do I do it from another worksheet with a formula?