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

1
votes

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.

2
excel
COLUMN($A:$D) will give you the position within columns C:F. – user4039065

2 Answers

3
votes

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.

position_within

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.

position_within_columns

2
votes
{=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))}