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

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

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

Thanks Biff

Another great solution

You're welcome. Thanks for the feedback!

Biff

"Oldersox" wrote in message
...
Thanks Biff

Another great solution

Thanks Ron. This is exctly what I needed.