How to get last non-empty item from a column in Microsoft Excel spreadsheet

If your MS-Excel spreadsheet data contains daily transactions, daily updates or any chronological items, you probably want to retrieve latest/last item from a particular column. There are a couple of methods to achieve this.

1. Using INDEX() and COUNTA() functions.
2. Using LOOKUP() function.

Method 1: Using INDEX() and COUNTA() functions

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

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

As shown in above figure, applying the given formula will give the last item of column A, which is “5-Jan-17”. Whoa! This seems pretty nice and short but, wait. This formula can’t deal with the intermixed blank cells with the values. Well, the second method will rescue you when there are blank cell in-between the values in a column. Here it is!

FYI: The only difference between COUNT() and COUNTA() functions is, COUNT() only counts the numbers/numerals while COUNTA() counts all nonempty cells irrespective of the type of data.

Method 2: Using LOOKU() function

Syntax: =LOOKUP(lookup_value, lookup_vector, [result_vector])

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

Yes, I know the formula has blown your mind. You are not getting why there is “2” as the first argument. Why are we doing division in the second argument? Let’s understand this weird formula, which is not actually.

1. The expression in second argument, A:A<>"", returns a list/array of true and false values: {TRUE, FALSE, TRUE,...}.
2. The number “1” is then divided by this list/array and creates a new list/array composed of either 1's or 0’s (0 is due to divide by zero errors). Hence, the resultant list/array will be like {1,0,1,...} and that’ll be our lookup_vector.
3. Whenever the lookup_value can’t be found, LOOKUP() will match next smallest value.
4. In our case, “2” is our lookup_value, which is not present or never be present in our lookup_vector hence, LOOKUP will try to match next smallest value than “2” which is “1”.
5. Thus, LOOKUP will match the last “1” in the list/array.
6. LOOKUP returns the corresponding value in result_vector. Hence, in our case, LOOKUP will return “5-Jan-2017” as shown in above figure.

This is the reason behind keeping “2” deliberately as ‘lookup_value’.

Notwithstanding the intermixed blank cells in the column, this method exceptionally gives the right result.

Take a look at my other blogs - One of their own kind, The Space Ecstasy

SHARES

Share

Pin it

Tweet

Share

Share

Share

Buffer

Print

About Unknown