IF or similar but true result to display in consecutive row

[Oldersox]

Im looking for something along the lines of IF but I want to return the
succesful results in consecutive rows. I cannot use filter or sort as
original data needs to be displayed on the same sheet in its original order.
eg if cell in Column A = X then return value from same row Column C in the
next available row in Column E.

Column A Column B Column C Column D Column E
Row A X ABC ABC
Row B X DEF GHI
Row C X GHI MNO
Row D X JKL
Row E X MNO

 

[Ron Coderre]

Try this:

E1:
=INDEX($C$1:$C$10,SUMPRODUCT(SMALL((($A$1:$A$10="X")*ROW($A$1:$A$10))+(($A$1:$A$10="")*10^99),ROWS($E$1:E1))))

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP

 

[T. Valko]

Try this:

Entered as an array using the key combination of CTRL,SHIFT,ENTER (not just
ENTER):

=IF(ROWS($1:1)<=COUNTIF(A$1:A$5,"x"),INDEX(C$1:C$5,SMALL(IF(A$1:A$5="x",ROW(A$1:A$5)-MIN(ROW(A$1:A$5))+1),ROWS($1:1))),"")

Copy down until you get blanks.

Biff

 

[Oldersox]

Thanks Ron. This is exctly what I needed.

 

[Oldersox]

Thanks Biff

Another great solution

 

[T. Valko]

You're welcome. Thanks for the feedback!

Biff