I have a row of text (not sure if this is relevant, but the text isn't purely typed, it is a formula of concatenations and if statements).

Some cells appear blank (although they still have the same ifelse/concatenation formula, but end up being "")

So, basically I have a row of formulaic text that looks like this

A1 B1 C1 D1 E1 F1 G1 H1 I1 J1

I want to use the range E1 through H1, and write a formula in a different worksheet that will order these by non-blank cells. So, I need to find the first nonblank cell, 2nd nonblank cell, 3rd, and 4th. Because some of these cells are blank, not all 4 of the spots will be fill, but the formula needs to make sure that if there is text in all of them then it will fill in order.


=IF(COUNTIF(E1:H1,"?*")>=2,INDEX(E1:H1,SMALL(IF(E1 :H1<>"",COLUMN(E1:H1)-COLUMN(E1)+1),1)),"") (for the first nonblank)

=IF(COUNTIF(E1:H1,"?*")>=2,INDEX(E1:H1,SMALL(IF(E1 :H1<>"",COLUMN(E1:H1)-COLUMN(E1)+1),2)),"") (for the 2nd nonblank), etc.

I have been trying this array formula, and it works for finding the first two non-blank text values, but when I try to find the 3rd and 4th nonblank values, if there is a blank value, instead of just a blank I am getting #NUM!.

Any ideas?