Microsoft

Return the last item in an Excel column

Use this Excel formula to easily return the last item from any list.

If you work with chronological lists or daily updates, you might also track the last item in a constantly changing list of values. Fortunately, you can combine Excel's INDEX() and COUNTA() functions to return the last value in a list by using the following expression:

=INDEX(column, COUNTA(column), 1)

For instance, to return the last value in column A, you'd use the expression

=INDEX(A:A, COUNTA(A:A), 1)

As you update the list, the formula automatically compensates.

october2008officeblog7fig1r.jpg

The one thing this formula can't handle is an empty cell. For instance, the formula for column B (cell G3) returns 0 when it encounters the empty cell (at cell C9).

october2008officeblog7fig2r.jpg

However, if you enter 0, instead of leaving the cell blank, the formula returns the correct value, 0.073.

october2008officeblog7fig3r.jpg

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

12 comments
fmarshall
fmarshall

It works for me as long as the cell is in the current worksheet.  But, wWhat if you want the result in another worksheet?  That doesn't seem to work....

Nappe2000
Nappe2000

I simply cannot get the above working. I have a worksheet with a graph where i type in my mtb rides. milage and time and so on. I want it to return my last/latest typen time to a cell. I tried all the different options with index and lookup and what not and nothing worked. So back to basic. Now i opened up a new workbook. just typed some random numbers in Row A and put in the above formula in B1 and i get the "the formula you just typed contains an error", Help please i both tried hitting enter and hitting shift, ctrl, enter. Using excell 2010

adamtsiopani
adamtsiopani

Thanks for this great tip. Is it also possible to include the formatting of the last cell in the column? For example, if the column has colour scale conditional formatting applied to it - where lowest values are red and highest are green.  How to include the colour of the last cell as well as the value?

Ding_gi
Ding_gi

I need a formula that can find the last occurrence of each item in a list. Scenario: Column A(Products) Column B(Date of Arrival) Socks------------------------------------Sept 2 Shoes------------------------------------Sept 3 Hats--------------------------------------Sept 3 Shoes------------------------------------Sept 4 Shoes------------------------------------ Shirts-------------------------------------- I need to get via excel formula, the latest Date of arrival per item say Sept 2 for "Socks", Sept 3 for "Hats", and Sept 4 for "Shoes". Please note that "Shoes" here has 2 Date of Arrivals as compared to "Socks" and "Hats" that have only one Date of Arrival for each. If you will notice, "Shoes" is listed 3 times wherein the last one has blank Date of Arrival. Also, "Shirts" is listed but it has blank Date of Arrival. I need then a formula that will return the desired latest Date of arrivals as I indicated above and will just return blank for the "Shirts" since it has no Date of Arrival. To summarize the desired result: Product Latest Date of Arrival Socks----------------------Sept 2 Shoes----------------------Sept 4 Hats------------------------Sept 3 Shirts----------------------[Blank] Please help. Thanks.

aiiacelite
aiiacelite

The values in the collumn are all as results of formulas and none of the options above seems to work for me. Is there any way out?

aiiacelite
aiiacelite

How do i find the last formular resulting value in a given collumn

wkintz
wkintz

Bravo! Thank you for restoring my sanity!

m.d.barron
m.d.barron

If the MATCH function is used in conjunction with the INDEX function, there is no need to add the dummy data: To find the last numerical value in a column: =INDEX(C:C,MATCH(3E+100,C:C,1)) To find the last text value in a column: =INDEX(C:C,MATCH("ZZZZZZZZZZ",C:C,1)) To find the last value in a column (numerical or text) - both kinds of data would need to be present, otherwise an error value is returned: =INDEX(C:C,MAX(MATCH(3E+100,C:C,1),MATCH("ZZZZZZZZZZ",C:C,1))) The error checking formula: =INDEX(C:C,MAX(IF(ISNA(MATCH(3E+100,C:C,1)),0,MATCH(3E+100,C:C,1)),IF(ISNA(MATCH("ZZZZZZZ",C:C,1)),0,MATCH("ZZZZZZZ",C:C,1))))

jeffcrtra
jeffcrtra

@fmarshall  It does work from another sheet.
I use it as such: =INDEX('Sheet2'!$A$7:$A$1000, COUNTA('Sheet2'!$A$7:$A$1000), 1) because my data starts at cell A7.

Go ahead give it a try.

wizard57m-cnet
wizard57m-cnet

rather than tagging on to a 4 year old zombie thread. The 'Discussion' forum is for matters of general discussion, not specific problems in search of a solution. The 'Water Cooler' is for non-technical discussions. You can submit a question to 'Q&A' here: http://www.techrepublic.com/forum/questions/post?tag=mantle_skin;content There are TR members who specifically seek out problems in need of a solution. Although there is some overlap between the forums, you'll find more of those members in 'Q&A' than in 'Discussions' or 'Water Cooler'. Be sure to use the voting buttons to provide your feedback. Voting a '+' does not necessarily mean that a given response contained the complete solution to your problem, but that it served to guide you toward it. This is intended to serve as an aid to those who may in the future have a problem similar to yours. If they have a ready source of reference available, perhaps won't need to repeat questions previously asked and answered. If a post did contain the solution to your problem, you can also close the question by marking the helpful post as "The Answer".

aiiacelite
aiiacelite

are you sure about what you are saying?

romanma
romanma

Hi, Thank you for the above. It is very helpful. I was wondering if you could help me with something. I am using: To find the last numerical value in a column: =INDEX(C:C,MATCH(3E+100,C:C,1)) which is working great for returning the last numerical value of an array. How can I change this to use the second to last or third to last etc? Any way this can be done? Thanks for any help, Roman

Editor's Picks