Last non-empty cell in a column

Question

Does anyone know a formula to find the value of the last nonblank cell in a column in Microsoft Excel?

Answer 1

This works with both text and numbers, and it doesn't matter if there are empty cells, i.e. he will return the last non-empty cell.

It must be entered into the array , which means that you press Ctrl-Shft-Enter after , type or paste it. Column A is below:

=INDEX(A:A,MAX((A:A<>"")*(ROW(A:A)))) 

Answer 2

Using the following simple formula is much faster.

 =LOOKUP(2,1/(A:A<>""),A:A) 

For Excel 2003:

 =LOOKUP(2,1/(A1:A65535<>""),A1:A65535) 

This gives you the following benefits:

• this is not an array formula
• it is not a volatile formula

Explanation:

• (A:A<>"") returns an array of {TRUE,TRUE,..,FALSE,..}
• 1/(A:A<>"") changes this array to {1,1,..,#DIV/0!,..} .
• Since LOOKUP expects a sorted array in ascending order and taking into account that if the LOOKUP function cannot find an exact match, it selects the largest value in lookup_range (in our case {1,1,..,#DIV/0!,..} ), which is less than or equal to the value (in our case 2 ), the formula finds the last 1 in the array and returns the corresponding value from result_range (the third parameter is A:A ).

Also a small note - cells with errors are not taken into account above the formula (you can see this only if the last non-empty cell has an error). If you want to take them into account, use:

 =LOOKUP(2,1/(NOT(ISBLANK(A:A))),A:A) 

The image below shows the difference:

Answer 3

Here is another option: =OFFSET($A$1;COUNTA(A:A)-1;0)

Answer 4

Inspired by the great leadership given by Doug Glensi's answer, I came up with a way to do the same without the need for an array formula. Don't ask me why, but I try to avoid using array formulas if at all possible (not for any specific reason, it's just my style).

There he is:

 =SUMPRODUCT(MAX(($A:$A<>"")*(ROW(A:A)))) 

To search for the last non-empty row using column A as the reference column

 =SUMPRODUCT(MAX(($1:$1<>"")*(COLUMN(1:1)))) 

To find the last non-empty column using row 1 as a reference row

This can be additionally used in combination with the index function to efficiently define dynamic named ranges, but this is something for another post, as it is not related to the immediate issue being addressed here.

I tested the above methods with Excel 2010, both "natively" and in "compatibility mode" (for older versions of Excel), and they work. Again, with them you do not need to do any of Ctrl + Shift + Enter. Using the way sumproduct works in Excel, we can get around the need to transfer operations with arrays, but we do this without an array formula. I hope someone out there appreciates the beauty, simplicity and elegance of these proposed sumproduct solutions as much as I do. However, I do not confirm the effectiveness of these solutions. It’s just that they are simple, look beautiful, help as intended and are flexible enough to expand their use for other purposes :)

Hope this helps!

All the best!

Answer 5

I know this question is old, but I am not satisfied with the answers provided.

• LOOKUP, VLOOKUP and HLOOKUP have performance problems and should never be used.

• Array functions have a lot of overhead and may also have performance issues, so it should only be used as a last resort.

• COUNT and COUNTA run into problems if the data is not contiguously non-empty, i.e. you have spaces and then the data again in the appropriate range

• INDIRECT is unstable, therefore it should be used only as a last resort

• OFFSET is volatile, so it should only be used as a last resort

• any references to the last possible row or column (for example, 65536th row in Excel 2003) are not reliable and lead to additional overhead

This is what I use

• when the data type is mixed: =max(MATCH(1E+306,[RANGE],1),MATCH("*",[RANGE],-1))

• when he knew that the data contained only numbers: =MATCH(1E+306,[RANGE],1)

• when he knew that the data contained only text: =MATCH("*",[RANGE],-1)

MATCH has the lowest overhead and is non-volatile, so if you are working with a lot of data, this is best used.

Answer 6

This works in Excel 2003 (& later with a little editing, see below). Press Ctrl + Shift + Enter (not just Enter) to enter it as an array formula.

 =IF(ISBLANK(A65536),INDEX(A1:A65535,MAX((A1:A65535<>"")*(ROW(A1:A65535)))),A65536) 

Keep in mind that Excel 2003 failed to apply an array formula to the entire column. It gives #NUM! ; unpredictable results may occur! (EDIT : Conflicting information from Microsoft: the same may or may not be true regarding Excel 2007, the problem can be fixed in 2010. )

That's why I apply the array formula to the range A1:A65535 and give special processing to the last cell, which is A65536 in Excel 2003. You can't just say A:A or even A1:A65536 , since the latter will automatically return to A:A

If you are absolutely sure that the A65536 empty, you can skip the IF part:

 =INDEX(A1:A65535,MAX((A1:A65535<>"")*(ROW(A1:A65535)))) 

Please note that if you are using Excel 2007 or 2010, the last line number is 1048576, not 65536, so adjust if necessary.

If there are no empty cells in the middle of your data, I would just use a simpler formula, =INDEX(A:A,COUNTA(A:A)) .

Answer 7

An alternative solution without array formulas, possibly more reliable than the previous answer with a solution (hint to) without array formulas , is

 =INDEX(A:A,INDEX(MAX(($A:$A<>"")*(ROW(A:A))),0)) 

See this answer for an example. Kudos to Brad and barry houdini who helped resolve this issue .

Possible reasons for preferring a formula without an array are given in:

• The official Microsoft page (look at the "Disadvantages of using array formulas").
  Array formulas may seem magical, but they also have some disadvantages:

  • Sometimes you may forget to press CTRL + SHIFT + ENTER. Remember to press this key combination whenever you enter or edit an array formula.
  • Other users may not understand your formulas. Array formulas are relatively undocumented, so if other people need to change their books, you must either avoid array formulas or make sure that these users understand how to change them.
  • Depending on the processing speed and memory of your computer, large array formulas can slow down computations.

• Array Formula Heresy .

Answer 8

You can find the last non-empty value in column A using formula (1).

Formula 1

 =INDIRECT("A" & SUMPRODUCT(MAX((A:A<>"")*(ROW(A:A))))) 

You can add a specific range to formula (1) instead of searching the entire column (which may be computationally inefficient) using formula (2).

Formula 2

 =INDIRECT("A" & SUMPRODUCT(MAX(($A$1:$A10<>"")*(ROW($A$1:$A10))))) 

And finally, you can determine the column number of the last non-empty cell using formula (3).

Formula 3

 SUMPRODUCT(MAX(($A$1:$A10<>"")*(ROW($A$1:$A10)))) 

Answer 9

=INDEX(A:A, COUNTA(A:A), 1) taken from here

Answer 10

=MATCH("*";A1:A10;-1) for text data

=MATCH(0;A1:A10;-1) for numeric data

Answer 11

Ive tried all non-volatile versions, but not a single version above worked. excel 2003 / 2007update. Of course, this can be done in excel 2003. Not as an array or standard formula. I either get only an empty, 0 or # error value. Therefore, I resort to unsustainable methods. It worked.

= LOOKUP (2.1 / (T4: T369 <> ""), T4: T369)

@Julian Kroné .. Using ";" instead of "," DOES NOT work! I think you are using Libre Office and not MS Excel? LOOKUP is so annoying by will that I use it only as a last resort

Answer 12

Put this code in the VBA module. Save. According to the functions, the user will define this function.

 Function LastNonBlankCell(Range As Excel.Range) As Variant Application.Volatile LastNonBlankCell = Range.End(xlDown).Value End Function 

Answer 13

I used hlookup

A1 has a date; A2:A8 has forecasts made at different times, I want the latest

 =Hlookup(a1,a1:a8,count(a2:a8)+1) 

It uses the standard hlookup formula with a search array determined by the number of entries.

Answer 14

for text data:

 EQUIV("";A1:A10;-1) 

for numerical data:

 EQUIV(0;A1:A10;-1) 

This will give you the relative index of the last non-empty cell in the selected range (here A1: A10).

If you want to get this value, refer to it through INDIRECT after creating -textually - an absolute cell reference, for example:

 INDIRECT("A" & (nb_line_where_your_data_start + EQUIV(...) - 1)) 

Answer 15

I had the same problem. This formula also works equally well: -

 =INDIRECT(CONCATENATE("$G$",(14+(COUNTA($G$14:$G$65535)-1)))) 

14 is the line number of the first line in the lines you want to count.

Chronic Cowl

Answer 16

If you know that there should not be empty cells between them, this is the fastest way.

 =INDIRECT("O"&(COUNT(O:O,"<>"""))) 

It simply counts non-empty cells and refers to the corresponding cell.

It can also be used for a specific range.

 =INDIRECT("O"&(COUNT(O4:O34,"<>""")+3)) 

Returns the last non-empty cell in the range O4: O34.

Answer 17

For Microsoft Office 2013

"Last but one" from a non-empty string:

 =OFFSET(Sheet5!$C$1,COUNTA(Sheet5!$C:$C)-2,0) 

The "last" non-empty line:

 =OFFSET(Sheet5!$C$1,COUNTA(Sheet5!$C:$C)-1,0) 

Answer 18

This formula worked with me for office 2010:

= VIEW (2; 1 / (A1: A100 <> ""); A1: A100)

A1: first cell A100: refer to the last cell when comparing

Answer 19

I think the answer from W5ALIVE is closest to what I use to find the last row of data in a column. Assuming I'm looking for the last row with the data in column A, I would use the following for a more general search:

 =MAX(IFERROR(MATCH("*",A:A,-1),0),IFERROR(MATCH(9.99999999999999E+307,A:A,1),0)) 

The first MATCH will find the last text cell, and the second MATCH will find the last numeric cell. The IFERROR function returns zero if the first MATCH finds all number cells or if the second match finds all text cells.

This is basically a small variation of the W5ALIVE mixed text and number solutions.

When testing time, it was significantly faster than the equivalent variations of LOOKUP.

To return the actual value of this last cell, I prefer to use indirect cell binding as follows:

 =INDIRECT("A"&MAX(IFERROR(MATCH("*",A:A,-1),0),IFERROR(MATCH(9.99999999999999E+307,A:A,1),0))) 

The method suggested by sancho.s may be a cleaner option, but I would change the part that finds the line number:

 =INDEX(MAX((A:A<>"")*(ROW(A:A))),1) 

the only difference is that ", 1" returns the first value, and ", 0" returns the entire array of values ​​(all but one that are not needed). I still prefer to access the cell's index function there, in other words, return the cell value with:

 =INDIRECT("A"&INDEX(MAX((A:A<>"")*(ROW(A:A))),1)) 

Great topic!

Answer 20

If you are not afraid to use arrays, then the following very simple formula to solve the problem:

= SUM (IF (A: <> "", 1.0))

You must press CTRL + SHIFT + ENTER because it is an array formula.

Answer 21

A simple one that works for me:

 =F7-INDEX(A:A,COUNT(A:A)) 

Answer 22

Ok, so I had the same problem as mine, and tried both of the best answers. But just getting formula errors. It turned out that I needed to exchange "," for ";" for the formulas to work. I am using XL 2007.

Example:

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

or

 =INDEX(A:A;MAX((A:A<>"")*(ROW(A:A)))) 

Answer 23

For version tracking (adding the letter v at the beginning of the number), I found this to work well in Xcelsius (SAP Dashboards)

 ="v"&MAX(A2:A500)