ROW(B1) within the SMALL function denotes the 1st smallest, 2nd smallest etc;
and so that should be ROW(B1) instead of ROW(B152) which denotes 152nd
smallest

If this post helps click Yes
---------------
Jacob Skaria


"jc132568" wrote:

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