Excel Guides

Retrieving the Last Value in a Column in Excel

There are a few different ways to retrieve the last value in a column in Excel. One way is to use the LOOKUP function. The LOOKUP function will return the last value in a column if the column is sorted in ascending order. For example, if you have a column of numbers and you want to find the last value, you can use the following formula:

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

Another way to retrieve the last value in a column is to use the INDEX/MATCH function. The INDEX/MATCH function is a bit more versatile than LOOKUP and can be used to find the last value in a column regardless of how the column is sorted. The following formula will return the last value in column A:

=INDEX(A:A,MATCH(REPT("z",255),A:A))

If you want to find the last non-empty cell in a column, you can use a variation of this formula:

=INDEX(A:A,MATCH(REPT("z",255),A:A)-1)

Move beyond 

Excel

Get started with Causal today.
Build models effortlessly, connect them directly to your data, and share them with interactive dashboards and beautiful visuals.