array "formula maybe too long" excel 2007

[standaman7]

afternoon,

i have the formula below to pull from an array. the problem is that the data im using for the array contains 20000+ rows. The function works fine for about the first 10 lines and then i get random data that is pulled from my data source. any help?



{=IF(ROWS($C2:$C2)>$A2,"",INDEX('XX File'!$A$2:$A$29428,SMALL(IF(('XX File'!$F$2:$F$29428<='Input'!$A2)*('XX File'!$G$2:$G$29428>='Input'!$A2)*('XX File'!$A$2:$A$29428='Input'!$B2),ROW('XX File'!$A2:$A29428)-ROW('XX File'!$A$2)+1),ROWS(C2:C2))))}

 

[Robert Mika]

afternoon,

i have the formula below to pull from an array. the problem is that the data im using for the array contains 20000+ rows. The function works fine for about the first 10 lines and then i get random data that is pulled from my data source. any help?



{=IF(ROWS($C2:$C2)>$A2,"",INDEX('XX File'!$A$2:$A$29428,SMALL(IF(('XX File'!$F$2:$F$29428<='Input'!$A2)*('XX File'!$G$2:$G$29428>='Input'!$A2)*('XX File'!$A$2:$A$29428='Input'!$B2),ROW('XX File'!$A2:$A29428)-ROW('XX File'!$A$2)+1),ROWS(C2:C2))))}

{=IF(ROWS($C2:$C2)>$A2,"",INDEX('XX File'!$A$2:$A$29428,SMALL(IF(('XX File'!$F$2:$F$29428<='Input'!$A2)*('XX File'!$G$2:$G$29428>='Input'!$A2)*('XX File'!$A$2:$A$29428='Input'!$B2),ROW('XX File'!$A2:$A$29428)-ROW('XX File'!$A$2)+1),ROWS($C$2:C2))))}

 

[standaman7]

That adjustment for the formula worked for the first line and then after, i got #NUM!. the first part of the formula "IF(ROWS($C2:$C2)>$A2,"" is a reference to the below sumproduct, dont know if that helps:

=SUMPRODUCT(('XX File'!$F$2:$F$29428<='Input'!$A2)*('XX File'!$G$2:$G$29428>='Input'!$A2)*('XX File'!$A$2:$A$29428='Input'!$B2))

 

[Robert Mika]

You need to lock this as well:
IF(ROWS($C$2:$C2)>$A$2