Our Recommendation for You Search your Query, You can find easily. for example search by book name or course name or any other which is related to your education

Followers

Return Last filled cell from same Row

Display last or last filled cell in the same row

O 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 tutorial I will show how we can display result data from the last (latest) filled the cell was that the number, value.

In these examples in the picture below you may notice several display modes (copied) the data from the last filled cells 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

Last non empty cells or last filled cells in the same row (Last NonBlank cell)

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 cells 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)))

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 cells from the same 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 has displayed the value of each second last 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 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 contains the last filled cells in the same row

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));"")

Return the column header for the first blank cell after the last filled cells 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 filled cells from the same row

If you want to display the column number where there is last filled cells 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 of the 'C' column third in a order.

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

Converting the number of columns in the column letter

If instead the number of columns you want to display a letter of the column headers then use the following formula.

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)))



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 the comma.

Other tutorials related to the last cell

- Last Data from Row or Column in Excel
- Find the Latest Date for duplicates
- Get Last Non Zero and Non Text cell from Column