Vlookup/Hlookup for whole table?

[AnimalCrackers]

I am attempting to make a "user friendly" lookup sheet where they can enter in a reference value (such as a baseball team name "Dodgers") and a table of data would be copied from a separate worksheet in the workbook. The table being copied would be multiple columns and multiple rows, it seems like the VLOOKUP and HLOOKUP tools are not capable of handling this sort of task.

If this seems confusing, essentially the end product would have the top row labelled as "Dodgers", 2nd row would be various stats assigned to their own column (Player name, Runs, HR, AVG, SB), 3rd row would be the actual data (Player#1, 102, 39, .312, 9), 4th row would be the same as third row but filled in with Player#2 data.

All the data would already be entered into the master worksheet and only needs to be referenced/copied forward to the main sheet. I can do this through visual basic programming, but wanted to know if there was a faster/easier excel tool available like VLOOKUP that allows for multiple columns under a single reference to be copied.

Thanks.

 

[rallcorn]

Try a PivotTable built off of the source data with the "Team" field in Report Filter.

 

[AnimalCrackers]

Thank you for the fast response. That would certainly work, but from a functionality standpoint I was hoping there was a way to keep the table data separate from the display data. I'm not sure it's possible with just a generic excel tool and I may just have to revert to VBA programming.

*Edit* saw the error in my response since the pivottable will spit out a separate data table, however I was hoping to find some tool like VLOOKUP or HLOOKUP, or even using something like HLOOKUP and then reference the copied cells for the remaining values?