Hi,

Am Sat, 13 Jul 2013 14:58:59 -0700 (PDT) schrieb :

Hi, Claus, when I make the reference relative it gives some incorrect results and some #NUM! error messages. That's why I decided to try making it into an absolute reference. I was surprised that it worked with an absolute reference, but it seems to. I'm not sure why. I'm not very good with formulas.

change the formula:
=IFERROR(INDEX($A$1:$A$100,SMALL(IF($B$1:$G$100="A pples",ROW($1:$100)),ROW(A1))),"")
and enter with CTRL+Shift+Enter
Now when you copy down you get empty cells if no more entry with Apples
exists.


Regards
Claus B.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2