I am using the following to pull out data based on the presence of text in a
particular column:
In B2
=IF(ISERROR(SMALL($F:$F,ROW(B1))),"
",INDEX(PC3005!B:B,MATCH(SMALL($F:$F,ROW(B1)),$F:$ F,0)))
In F2
and =IF(TRIM(PC3005!Y2)<"",ROW()," ")

It works great and is part of our everyday sheets. I am adapting this for
another worksheet application where I want to achieve this same function but
further down the worksheet, say in B152 and F152. The formula doesn't work!
Do I have to have the formula at the top of the worksheet for it to work.
What have I missed?

=IF(ISERROR(SMALL($F:$F,ROW(B152))),"
",INDEX(AntibacterialDrugs!A:A,MATCH(SMALL($F:$F,R OW(B152)),$F:$F,0)))

=IF(TRIM(AntibacterialDrugs!O2)<"",ROW()," ")

Thanks
jc