=OFFSET(Teams!$A$1,SMALL(IF(Teams!$C$5:$C$27="Play ing",ROW(Teams!$C$5:$C$27)
),ROW(Teams!$A1))-1,Column(Teams!D$1))

should do what you want.

If the source cell is array entered, dragging will maintain it as an array
formula.

--
Rgards,
Tom Ogilvy

wrote in message
oups.com...
Is there a fast and easy way to do this.
I am use the following array (provided by Tom Olgivy) to pick up the
contents of a cell, which returns it to another cell.


=OFFSET(Teams!$A$1,SMALL(IF(Teams!$C$5:$C$27="Play ing",ROW(Teams!$C$5:$C$27)
),ROW(Teams!$A1))-1,4)

It works well but the problem is that I wish to extend the formula to
retrieve additional info in other columns and rows but the drag
selection does not see to work with it, i.e. I would like to drag the
formula across so the last col no changes from 4 to 5) and then I would
like to drag the formula down 11 rows where ',ROW(Teams!$A1))-1,5)'
increments like ,ROW(Teams!$A2))-1,5), ROW(Teams!$A3))-1,5),
ROW(Teams!$A4))-1,5) and so on.

I can get round it by typing it in but I need to apply this logic to a
large number of cells which use this formula, which means there is a
lot of work ahead. Is there an easier way of going about it?

Grateful for advice
Thanks
Tony