Complicated Table HELP!

My current project at work is requiring me to create a table that will perform a lookup and return every row that matches the lookup value. Not only this, but it needs to be able to scale with the data it is pulling from. Let me make an example

I dump into excel a sheet with 100 rows. 50 of those rows has the word APPLE in the first column, and 50 has the word ORANGE. The table on another sheet will lookup and return THE ENTIRE ROW for every row that started with APPLE.

Now I've had to make another dump, this time with 110 rows. 60 of those rows now says APPLE. The table on the other sheet will automatically adjust its size, and return the full 60 rows that started with the row APPLE.

Basically, these functions need to:
1. Adjust to include all the data in the new dumps that will be done.
2. Return the entire row of data, instead of just the single cell. (I would like to be able to do it without having an array function in every single cell.)
3. Return all rows that include the lookup, and adjust the end of the table to fit the data.

Dummy book.xlsx

Thank you. I went ahead and put together a dummy book, along with comments that attempt to describe what I'm looking for. It's mostly a matter of helping look up certain data, returns rows, and move it into different tabs. Alot of the data will be summed and averaged and mathed by I don't think that should mess with the general look up process.