Try it like this (array entered):

=IF(ROWS(A$43:A43)<=COUNTA(A$17:A$26),INDEX(A$17:A $26,SMALL(IF(A$17:A$26<"",ROW(A$17:A$26)-MIN(ROW(A$17))+1),ROWS(A$43:A43))),"")

--
Biff
Microsoft Excel MVP


"shorticake" wrote in message
...
I have the following text in column A rows 17 through 26

17 PY02
18
19 PY04
20 PY05
21 PY06
22 PY07
23 PY08
24 PY09
25 PY10
26 PY11

Then I use this formula
=IF(ROWS($1:1)<=COUNTA(A17:$A$26),INDEX($A$26,SMAL L(IF($A$26<"",ROW($A$26)-MIN(ROW($A$26))+1),ROWS($1:1))),"")
in A43 and copy down to A52.

I'm expecting it to return the following in Column A rows 43:52

43 PY02
44 PY04
45 PY05
46 PY06
47 PY07
48 PY08
49 PY09
50 PY10
51 PY11
52

But I'm getting this instead:

43 PY02
44 PY05
45 PY06
46 PY08
47 PY10
48
49
50
51
52

I don't know why it's skipping PY04, PY07, PY09, and PY11.

Any help is greatly appreciated!