Who knows the easiest path

I've accomplished the task below with many long and difficult formulas. In "difficult" I mean trying to explain step by step procedures in building this spreadsheet to an excel novice. Does anyone know of a simple formula that will get these results without using a macro? There is always an easier way of doing things, escpecially in excel.

Is this formula to long ??


=IF(ROW()-ROW(C$2)+1>COUNTIF($A$2:$A$10,"Apples"),"",INDEX($A$2:$A$10,MATCH("Apples",$A$2:$A$10,0)))

File attached here

http://cjoint.com/?iAxscAE57h

VBA Noob

No, not too long at all. Thank you very much.

Originally Posted by VBA Noob

Is this formula to long ??


=IF(ROW()-ROW(C$2)+1>COUNTIF($A$2:$A$10,"Apples"),"",INDEX($A$2:$A$10,MATCH("Apples",$A$2:$A$10,0)))

File attached here

http://cjoint.com/?iAxscAE57h

VBA Noob

Not sure of the purpose of the formula but the "ROW(C$2)+1" bit will always be 1 - so yes it is too long

Your right

Could be shorten to

=IF(ROW(C1)>COUNTIF($A$2:$A$10,"Apples"),"",INDEX($A$2:$A$10,MATCH("Apples",$A$2:$A$10,0)))

VBA Noob