Copy First Non Blank Cell from Row or Column

Copy Column Name or Header of Column in which the first Non-Empty Cells in the same Row

If you are wondering how you can display a column heading for the first filled cell in one row then use this formula below.

ARRAY formula in cell I2 is as follows. (Formula copy down)

=IF(L2<>"";INDEX($A$1:$G$1;SMALL(IF($A2:$G2<>"";COLUMN($A2:$G2)-COLUMN($A2)+1);INT((COLUMNS($L2:L2)-1)/2)+1));"")

How to Display the Value of the First Filled Cell in the Column or Row

In this Excel example I will show how we can copy or display the value or data from the first filled cell that is in the column or row. The situation is as shown below.

In the picture above you see that I put several formulas are copied value from the first filled cell was to be placed in the Row or Column. All of the formula below, perform the same task.

Return 1st Non Blank cell from Row

ARRAY formula in cell K2 in the figure above is as follows. (Formula copy down). This formula does not ignore zero (0) unlike other formulas.

=IF(INT((COLUMNS($K2:K2)-1)/2)+1<=COUNT($A2:$F2);INDEX($A2:$F2;SMALL(IF($A2:$F2<>"";COLUMN($A2:$F2)-COLUMN($A2)+1);INT((COLUMNS($K2:K2)-1)/2)+1));"")

The formula in cell L2 in the image above is as follows. (Formula copy down)

=INDEX(A2:G2;MATCH(TRUE;INDEX((A2:G2<>0);0);0))

ARRAY formula in cell M2 in the image above is as follows. (Formula copy down)

=INDEX(A2:G2;MATCH(1;IF(A2:G2<>0;IF(A2:G2<>"";1));0))

ARRAY formula in cell N2 in the image above is as follows. (Formula copy down)

=IFERROR(INDEX(A2:G2;MATCH(TRUE;A2:G2<>0;0));"")

Return 1st Non Blank Cell from Column

ARRAY formula in cell A8 on the image above is as follows. (Copy a formula to the right)

=IFERROR(INDEX(A$2:A$4;MATCH(1;IF(A$2:A$4<>0;IF(A$2:A$4<>"";1));0));"")

ARRAY formula in cell A9 on the image above is as follows. (Copy a formula to the right)

=IFERROR(INDEX(A$2:A$4;MATCH(TRUE;A$2:A$4>0;0));"")

Copy the First Filled Cell from Row but Ignore Zero (0) or an Empty Cell

If you have a situation like in the picture below, when the line contains zero or empty cell ie. not filled in the cell and you need to display or copy the first cell that contains the data or value then use the following formula below.

ARRAY formula in cell L1 in the image below as follows. (Formula copy down)

=IFERROR(INDEX(A1:J1;MATCH(TRUE;A1:J1<>0;0));"")

If you want to copy the value from the last cell then see the Excel tutorial Data from the last filled cell.