Formula that selects value from list in sequence order

[Scott]

I have a series of text data entries in P5149.
I also have data in some of the cells in Column I.

In Column K, I want to select the data from Column P in sequential order, IF
there is data in Column I. If there is not data in Column I, I want it to
advance to the next time data appears in Column I, without skipping the
selection order of Column P.

Hope that makes sense? Thanks!

 

[T. Valko]

Try this...

K4 = formula to count how many records meet the condition:

=COUNTA(I5:I149)

Enter this array formula** in K5 and copy down until you get blanks:

=IF(ROWS(K$5:K5)<=K$4,INDEX(P$5$149,SMALL(IF(I$5:I$149<>"",ROW(P$5$149)),ROWS(K$5:K5))-ROW(P$5)+1),"")

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

 

[T. Valko]

Try this...

K4 = formula to count how many records meet the condition:

=COUNTA(I5:I149)

Enter this array formula** in K5 and copy down until you get blanks:

=IF(ROWS(K$5:K5)<=K$4,INDEX(P$5$149,SMALL(IF(I$5:I$149<>"",ROW(P$5$149)),ROWS(K$5:K5))-ROW(P$5)+1),"")

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

 

[RagDyer]

If I understand your question, enter this in K5 and copy down as needed:

=IF(I5="","",INDEX(P$5$145,COUNTA($I$5:I5)))

 

[RagDyer]

If I understand your question, enter this in K5 and copy down as needed:

=IF(I5="","",INDEX(P$5$145,COUNTA($I$5:I5)))