Blue Flower

Display Last Non-Empty Cell From The Same Row

About cells and acts on them (selection cells, named cells, filling cells, moving cells, etc.) I wrote more on this website - tutorials (see the contents of topics on this site). In this Excel tutorial I will show how we can display Data as result from the last (latest) filled the cell was that the number, value.

In these Excel examples in the picture below you may notice several display modes (copied) the Data From the Last Filled Cell of the same row or see the letters column in which the first empty cell after the last filled cell.

Display data from the last filled cells in the same row or column header the first blank cell in the same row

Get Last NonBlank Cell From The Row

If you want to display or copy data from the last filled cells in the same row then look at the situation in the picture above and you see 'L' column.

The formula in cell 'L2' is as follows. (Copy formula down). This formula returns the value or number of the last filled cell in the same row.

=LOOKUP(2;1/(1-ISBLANK(B2:J2));B2:J2)

These two formulas below also return the same result as the previous one. (9,99999999999999E+307 is a limit to enter the maximum permissible positive number or value in Excel 2013)

=LOOKUP(9,99999999999999E+307;B2:INDEX(B2:J2;MATCH(9,99999999999999E+307;B2:J2)))

and ARRAY formula (CSE formula)

=IF(INT((COLUMNS($L2:L2)-1)/2)+1<=COUNT($B2:$J2);INDEX($B2:$J2;LARGE(IF($B2:$J2<>"";COLUMN($B2:$J2)-COLUMN($B2)+1);INT((COLUMNS($L2:L2)-1)/2)+1));"")

The Penultimate Filled Cell From The Row (Second Last Cell)

If you want to display the value or number of the penultimate or second last but one filled cell, you see in the picture above the column 'M' results returned by the formula. So, the formula is displayed value of each Second Last Cell or penultimate cell containing a value or a number.

The formula in cell 'M2' is the following. You notice in the formula number -2. This number indicates that we are looking for second filled cell of of the same row but also what we provided last filled cell of the same row. If you want to return a third filled cell (starting from the last filled cell), then replace this number with the number -3

=OFFSET(A2;0;COLUMNS(A2:J2)-COUNTBLANK(A2:J2)-2)

Return The Name Of The Column Header That Contain The Last Filled Cell

If you want to display the header or column name for the last filled cell then use the formula below. In the picture above you see the cell of 'N2' to 'N5'.

ARRAY formula in cell 'N2' is as follows. Formula finished with Ctrl+Shift+Enter (not just the Enter key).

=IF(LOOKUP(2;1/(1-ISBLANK(B2:J2));B2:J2)<>"";INDEX($B$1:$J$1;LARGE(IF($B2:$J2<>"";COLUMN($B2:$J2)-COLUMN($B2)+1);INT((COLUMNS($N2:N2)-1)/2)+1));"")

Get The Column Header For The First Blank Cell After The Last Filled Cell In The Same Row

If you want to know in which column of the first empty cell (the first cell is not filled in a row) in the same row then use the formula below. You notice in the picture above the cells of 'L8' to 'L11'. Also you notice a nested function COLUMNS ($L2:L2) (bold letters) that, depending on the column that contains the formula changes the column letter, although this has not necessarily. If a formula set in the column 'P' then change the letters in the address ($P2:P2).

ARRAY formula in cell 'L8' is as follows.

=IF(OFFSET(A2;0;COLUMNS(A2:J2)-COUNTBLANK(A2:J2)-2)<>"";INDEX($B$1:$J$1;LARGE(IF($B2:$J2<>"";COLUMN($B2:$J2)-COLUMN($B2)+2);INT((COLUMNS($L2:L2)-1)/2)+1));"")

Return the Column Number Where There is Last NonEmpty Cell From the Same Row

If you want to display the column number where there is last filled cell in the same row then use the formula below.

The formula in cell 'M8' is as follows. You notice that the result of this formula the number 3, which is identical to the column C, because the 'C' column third in a order.

=LOOKUP(2;1/(B2:J2<>"");COLUMN(B2:J2))

Converting the Number of Clumn in the Column Letter

If instead the number of columns you want to Display a Letter of the Column Header then use the following formula. Please see Excel tutorial example 'Return The Column Letter'.

The formula in cell 'N8' is as follows. This formula uses extra column 'M'

=CHAR(64+M8)

If you want to avoid the additional column, the formula is as follows. So we are nestled formula that returns the number of columns in the formula above.

=CHAR(64+LOOKUP(2;1/(B2:J2<>"");COLUMN(B2:J2)))

Also, If you want to display the value of the First Filled Cell then see the tutorial 'Copying Data from the First Non Empty Cell'

Other Excel tutorials related to the last cell

 

BTW: Pay attention, you may need as a separator of arguments formula use a comma (,) instead of a semicolon (;).
Copy formula to Notepad and with Find/Replace change semicolon to comma.