Option 2 works the best, and in your example I've put the following formula in cell D11 downwards ... =IF((ROW()-10)>COUNTIF($J$27:$J$32,$D$8),"",INDEX($K$27:$M$32,SMALL(IF($J$27:$J$32=$D$8,ROW($J$27:$J$32)-26),ROW()-10),MATCH("Team "&$D$9,$K$26:$M$26,0)))

This must be entered as an array formula, so instead of simply pressing Enter to put the formula into the cell you have to use Ctrl-Shift-Enter

Given the rather long nature of the formula you might be best of giving me an example which mirrors the final layout of your sheet so that I can adapt it for you.