I have just re-read your post and realize you may be looking for for
multiple rows from sheet 1, all having the same season number. If
that is true, you need a different approach.

For efficiency purposes, I would use a helper column in the second
sheet, say column A. Set aside a cell, say A1 to carry a count of the
instances of season number in the first sheet. Assume B1 contains
season number

A1: =COUNTIF(Sheet1!B:B,B1)

A2:
=IF(ROW(1:1)$A$1,"",SMALL(IF(Sheet1!$B$2:$B$1000= $B$1,ROW(INDIRECT("1:"&ROWS(Sheet1!$B$2:$B$1000))) ,""),ROW(1:1)))

Enter as array formula Ctl+Shift+Enter. Drag down as far as you think
you need to extract all shows from one season.

Then in

C2: =IF($A2="","",INDEX(Sheet1!A:A,$A2))

Drag down and across as far as necessary. Yo can delete the season
column from this range afterwards.

This should do it.

Sorry about the bum steer.

Declan O'R


--
DOR
------------------------------------------------------------------------
DOR's Profile: http://www.excelforum.com/member.php...o&userid=29088
View this thread: http://www.excelforum.com/showthread...hreadid=490341