A lookup function will always return one single value.
If you want to use this kind of functions to select sevearl lines of information, then you first need to prepare the data for this. As a result, you get a very heavy document.
But, if you want something totally dynamic, then this may be the correct way.
First Sort the data.
Next to the data, add a column, that will increment a counter for each new occurence. Let's say we're splitting the data depending on values in column C, and we use column A and B as "helpers", and the data begins in row 2:
Then, in B2, enter: "=if(c2=c1;b1+1;1)" and duplicate downwards in col B.
In A2 enter "=c2 & " " & b2
Now, In a separate sheet, enter the category name in A1, the column titles in row 2.
In A3 and downwards, fill in guiding values 1, 2, 3 etc as long as needed.
And the function to pick values to the second sheet:
In B3 "=vlookup($a$1 & " " & a3; Maindatasheet!$a$2:$h$300; 3; false)
In C3 "=vlookup($a$1 & " " & a3; Maindatasheet!$a$2:$h$300; 4; false)
Next, duplicate the distribution formulas downwards.
I may have mistyped something here. Use Excel help to doublecheck.
The if command has three parts: test, value if true, value if false. We use it to start numbering from 1 for each section.
vlookup searches among the values in the leftmost column. That's why we added the helper values in column A. The helper value is combined from the category label, a space and then the ordinal value. The 4th parameter "false" is needed to ensure that we only get complete matches.
Also very important is to correctly use the relative vs absolute references.
Good luck