# Display or Return the Last Filled Cell from Row or Column

About cells and acts on them (selection, naming, filling, moving, etc.) I wrote already on this website - tutorials (see the content of topics on this site). In this tutorial I will show how we can show which should result data from the past (last) filled cells was that the number, value or text.

Excel Example 1

In this example, the picture below you can spot two ways of displaying data from the last filled cell in a certain Column.

The first way I how to display data from the last filled cell in column 'A'. The data contained in the column can be values, numbers, text, symbols and the like. Using the ARRAY formula below we can show that the information is to filled in the last cell in column 'A'. The Array CSE formula you finish with Ctrl+Shift+Enter.

=INDEX(A1:A15;MAX(IF(LEN(A1:A15)>0;ROW(A1:A15);0));1)

This ARRAY formula ignores blank cells (unfilled cells). So you see that this formula in cell B2 returned result from the last filled cell in column 'A' (Google). Another way is to return as a result of the value or number in mixed data contained in the column. Look at the picture above and the column 'D'. In cell 'E5' is of the following Excel formula. This formula returns the number or value that we entered in the last column. The formula ignores the text.

=OFFSET(D5;MATCH(1E+306;D5:D18;1)-1;0)

## Display the last number in the column - the last filled cell with numerical values

If you want to display last filled out cell, which contains a number or value you use this formula below.

=LOOKUP(99^99;D5:D18)

=LOOKUP(MAX(D5:D18)+1;D5:D18)

## Display text from the last entered cell in column

If you have a column that contains text and you want to return the text of the last filled cell then use this formula below

=LOOKUP(REPT("Z";255);A2:A15)

## Return the last filled cell regardless of the type of data

If you are interested in copying, or display data from the last filled or non-empty cell, regardless of the type of data (text or number or value) then use the formula below

=LOOKUP(2;1/(D5:D18<>"");D5:D18)

## Display data from the last non-empty cell in column from several other worksheets

Excel example 2

If you have a need to copy the last digit entered or values from another worksheet from that column then use the formula below. In the picture below you see the situation. On the worksheet 'Master' we have in column 'A' names worksheets used in the formula. In the column 'B' is the result of a formula, which displays the last filled cell with a specific worksheet. The formula for copying data from the last filled cell for that column from multiple worksheets is.

=OFFSET(INDIRECT(A1&"!"&"\$A\$1");MATCH(1E+306;INDIRECT(A1&"!"&"\$A\$1"&":\$A\$41");1)-1;0)

Note, that the formula used in the worksheet name in column 'A' which is integrated into the formula. This formula set in cell 'B1' on the worksheet 'Master' and then copy down.

## Return data from the last the filled worksheet from a certain cell

Excel example 3

The situation in the picture below you see 'Master' worksheet on which we need display data from the cell 'A1' from other worksheets, but so the formula returns the last filled cell of a worksheet where the data is located. The calculation includes all five worksheets. In this Excel Example Tutorial, the worksheet 'Sheet4' cell 'A1' contains data ie number 400.

If you have a need as the situation below then use this formula as shown in the 'B1'

=INDIRECT("Sheet"&COUNTA(Sheet1:Sheet5!A1)&"!A1") ## Display the Last Date for the Two Conditions

Excel example 4

Let us take a situation where we need to go back last date if we have two conditions as shown below. Also, you notice that the conditions are not unique data which further complicates the search results, therefore, appear to be duplicates. In the picture below you see the data.

• Card issued product
• A product that is issued from storage
• Date of issue of products from warehouses

In cells 'C13' and 'C14' notice two conditions (blue font)

In cell 'C15' is ARRAY or CSE formula that you need finish with Ctrl+Shift+Enter

=MAX((A2:A11=C13)*(B2:B11=C14)*(C2:C11)) ## Copy data or value from each of the fifth column in the same row

Excel example 5

In a situation where we need to go back or display data from each cell of the fifth (5th) from the same row or data from each of the fifth column in the same row then we can use the formula below. Note the formulas that return data from every 5th cell in the same row as well as formulas that return data from the last filled column in the same row. The formula in Cell C5:

=IF(OFFSET(\$A2;;COLUMN(A:A)*5+1)=0;"";OFFSET(\$A2;;COLUMN(A:A)*5+1))

The formula in Cell C6:

=IF(OFFSET(\$A3;;COLUMN(A:A)*5+1)=0;"";OFFSET(\$A3;;COLUMN(A:A)*5+1))

ARRAY formula in Cell C8:

=INDEX(5:5;LARGE((5:5<>"")*(COLUMN(5:5));1))

ARRAY formula in Cell C9:

=INDEX(6:6;LARGE((6:6<>"")*(COLUMN(6:6));1))

Instead of the formulas in C8 and C9 cells, we can use the following formula

The formula in Cell C11:

=OFFSET(A5;0;MATCH(MAX(A5:W5)+1;A5:W5;1)-1)

The formula in Cell C12:

=OFFSET(A6;0;MATCH(MAX(A6:W6)+1;A6:W6;1)-1)