Fill a cell with the first non-empty entry in a set of columns (from left to right)

Question

1. I am trying to fill a cell with the first non-empty entry in a set of columns (from left to right).

   1. Example Sheet is given below.

2. Desired Sheet output is given below.

3. I am applying array formula =INDEX(B2:D2,MATCH(FALSE,ISBLANK(B2:D2),FALSE)) in column A and formula is seen in cells A2, A3, A4 as {=INDEX(B2:D2,MATCH(FALSE,ISBLANK(B2:D2),FALSE))} but I am getting the following result instead of desired one.

   

4. can someone help to point out where I am going wrong.

Answer 1

Use the AGGREGATE¹ function to find the first non-blank cell and pass that back into an INDEX function.

      

The standard formula in A2 is,

=INDEX($B2:$Z2, AGGREGATE(15, 6, COLUMN(A:Z)/SIGN(LEN($B2:$Z2)), 1))

Fill down as necessary.

¹ _{The AGGREGATE function was introduced with Excel 2010. It is not available in earlier versions.}

Your array formula should work as any of these,

=INDEX(B2:D2,MATCH(1, SIGN(LEN(B2:D2)), 0))
=INDEX(B2:D2,MATCH(0, --ISBLANK(B2:D2), 0))
=INDEX(B2:D2,MATCH(FALSE, ISBLANK(B2:D2), 0))

Array formulas need to be finalized with Ctrl+Shift+Enter↵. Once entered into the first cell correctly, they can be filled or copied down or right just like any other formula.