Array Formula Not Working

I am trying to extract rows of data from an Excel worksheet to another worksheet based upon two search criteria (a matching code and date). I have created the following array formula:

{=INDEX('Lost Opps'!$A$2:$P$694,SMALL(IF((INDEX('Lost Opps'!$A$2:$P$694,,1)='Control Sheet'!$H$26)*(INDEX('Lost Opps'!$A$2:$P$694,,16)='Control Sheet'!$D$4),MATCH(ROW('Lost Opps'!$A$2:$A$694),ROW('Lost Opps'!$A$2:$A$694)),""),ROWS($A$2:A2)),COLUMNS($A$2:A2))}

When I copy the formula down, I get the same data on each row in the target worksheet. I have noticed that the non anchored references in ROWS(A$2:A2) and COLUMNS($A$2:A2) do not change.

Any help would be gratefully received.

Many thanks,

Check that your Caluclation Options (on the Formulas tab) are set to Automatic, rather than Manual.

Hope this helps.

Pete

They are set to automatic so I guess this isn't the problen. Thanks,

Your cells are probably set as text? Reformat as General, hit F2 and Enter

It's now working. many thanks for the help received!

Care to share your solution with the board?