Hi, Claus, thanks for the new formula. This one works, too, but again only if I make the ROW(A1) into an absolute reference. I don't know if it makes a difference, but in the real spreadsheets I'm working with, I'm not using an individual word like "Apples" in the search, I'm using cell references that go all the way down the column. The actual formula as I just tested it looks like this:

{=IFERROR(INDEX($A$1:$A$100,SMALL(IF($B$1:$G$100=I 2,ROW($1:$100)),ROW($A$1))),"")}

And I copy it all the way down the column so that I2 changes to I3, then I4, and so on. But both this and the other formula seem to work great with the absolute references. I have no idea why. Array formulas are beyond me.





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