Row not coming across - formula help!!!

[fffzac]

Hi,

I am looking to bring data over from one sheet to another when it matches a certain value on the first sheet. The current formula I am using is:

{=IF(ISERROR(INDEX(Sheet2!$A$2:$K$4970,SMALL(IF(Sheet2!$A$2:$A$4970='Sheet1'!$C$14,ROW(Sheet2!$A$2:$A$4970)),ROW(1:1)),0)),"",INDEX(Sheet2!$A$2:$K$4970,SMALL(IF(Sheet2!$A$2:$A$4970='Sheet1'!$C$14,ROW(Sheet2!$A$2:$A$4970)),ROW(1:1)),5))}

The problem I am having is that the first row of data I need is not coming across to the sheet and the last row of data being brought across is from the next dataset.

I think its something to do with the ROW() but cant work it out.

Any ideas?

 

[Special-K99]

Looks like you're trying to retrieve a value if it exists else return blank.

Try

non array formula
=IFERROR(INDEX(Sheet2!A$2:A$4970,AGGREGATE(15,6,ROW(Sheet2!A$2:A$4970)/(Sheet2!A$2:A$4970=Sheet1!$C$14),ROWS(A$2:A4970)),1),"")

or array formula
=IFERROR(INDEX(Sheet2!$A$2:$A$4970,SMALL(IF((Sheet2!$A$2:$A$4970=Sheet1!$C$14),ROW($A$2:$A$4970)),ROW(A1))-(ROW(A$2)-1),1),"")