Index Function Error

I'm trying to pull data from a multiple spreadsheets into one and the below formula has worked for all of the spreadsheets apart from 1 where it just returns an error. I can't see any formatting differences on this workbook compared to the others and the file name is correct. If anyone has any idea why this is happening or if they can suggest how it can be fixed I would be externally grateful.

{=IFERROR(INDEX([TEST.xls]BZ!$AD$2:$AD$1009,SMALL(IF($O$1=[TEST.xls]BZ!$D$2:$D$1009,ROW([TEST.xls]BZ!$D$2:$D$1009)-ROW([TEST.xls]BZ!$D$2)+1),ROW(1:1))),"0")}

change to ...

=IFERROR(INDEX([TEST.xls]BZ!$AD$2:$AD$1009,SMALL(IF($O$1=[TEST.xls]BZ!$D$2:$D$1009,ROW([TEST.xls]BZ!$D$2:$D$1009)-ROW([TEST.xls]BZ!$D$2)+1),ROWS($1:1))),"0")

ROWS($1:1) not ROW(1:1)

I tried your suggestion but no joy. I don't think the formula is wrong as it does work for others so I think there is something different with the work book its accessing for this row (each row in the workbook has this formula but with a different file name, (each row represents a month)) as it works for other files.

It worked when I created "Test.xls" with worksheet "BZ". What results are you getting?

I'm getting the IFERROR function so 0. When I remove the IFERROR Function from the Formula I get #NUM! as a result.

Probably comparing text with numbers (or similar mismatch of data types). O1 vs Column D ?

I have managed to solve it although i'm not sure what the issues was. When I evaluated the Formula it wasn't able to locate O1 in Column D. Cell O1 was in a table and when I copied the data and pasted it outside of the table (I moved it to cell E23) so now it was looking for cell E23 in column D and it worked. I couldn't see an differences in the formatting of column D in any of the other spreadsheets but I guess there must have been at some level.

Any way all sorted thanks for your help it's much appreciated