IF or similar but true result to display in consecutive row
 

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

IF or similar but true result to display in consecutive row
 

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


"Oldersox" wrote:

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

IF or similar but true result to display in consecutive row
 

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" wrote in message
...
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

IF or similar but true result to display in consecutive row
 

Thanks Ron. This is exctly what I needed.

IF or similar but true result to display in consecutive row
 

Thanks Biff

Another great solution

IF or similar but true result to display in consecutive row
 

You're welcome. Thanks for the feedback!

Biff

"Oldersox" wrote in message
...
Thanks Biff

Another great solution