How to return the second non-blank cell from row excel for Mac

Question

To return the first non-blank cell in a row I use:

=INDEX(C1:F1,MATCH(TRUE,INDEX((C1:F1<>0),0),0))

However, how do you return the second non-blank cell in a row? I only have text in my cells.

When I attempt =INDEX(C1:F1,AGGREGATE(15,6,COLUMN(C1:F1)/SIGN(LEN(C1:F1)),2)), as suggested.

I don't get the next non-blank cell returned, I get F cell returned, even if there are others that should have been returned before it. If there is nothing in F, I get a 0.

Answer 1

{=INDEX(C1:F1, SMALL(IF(C1:F1 <> "", COLUMN(C1:F1) - 2), 2))}

https://exceljet.net/formula/get-nth-match-with-index-match

-2 is from -COLUMN(INDEX(C1:F1, 1, 1)) + 1 so the full array formula is:

{=INDEX(C1:F1, SMALL(IF(C1:F1<>"", COLUMN(C1:F1) - COLUMN(INDEX(C1:F1, 1, 1)) + 1), 2))}

Answer 2

Use the AGGREGATE function's SMALL subfunction.

=INDEX(A1:A13, AGGREGATE(15, 6, ROW(1:13)/SIGN(LEN(A1:A13)), 2))

The 2 is the k for the small subfunction. Replace it with ROW(2:2) and tighten up all of the other cell references if you want to fill down for the third, fourth, etc.

When returning the column index number back to the INDEX function, AGGREGATE function uses the COLUMN function instead of the ROW function.. COLUMN($A:$D) will give you the position within columns C:F; e.g. 1, 2, 3 or 4.