Hi,

Tom Ogilvy provided this excellent array formula for me a while ago
which looks up and returns data that contains the keyword "Playing" in
another worksheet. I have the formula in 5 columns and 11 rows. It
works great but it causes problems when I try to insert rows above the
formula, as it returns #N/A.

here is the formula...

=OFFSET(Teams!$A$1,SMALL(IF(Teams!$C$83:$C$105="Pl aying",ROW($C$83:$C$105)),ROW($A1))-1,4)

If I insert a row above it recalulates the formula to...

=OFFSET(Teams!$A$1,SMALL(IF(Teams!$C$83:$C$105="Pl aying",ROW($C$83:$C$106)),ROW($A1))-1,4)

Note that the first ROW has increased from $C$105 to $C$106.

Can someone help?

Thanks
Tony