How to Get the Last Value in a Column in Google Sheets

Use =LOOKUP(2,1/(A2:A<>""),A2:A) to return the last non-empty value in column A, even if there are blank rows in the middle.

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

The classic 'last non-blank' trick: 1/(A2:A<>"") makes 1s for filled cells, and LOOKUP(2,...) lands on the last one. Ignores gaps safely.

How it works

To grab the most recent entry in a column, you need the last non-empty cell. The LOOKUP(2,1/(range<>""),range) pattern works because looking up 2 in a list of 1s can never find an exact match, so LOOKUP returns the last valid position — the last filled row. If your data has no gaps, INDEX(A:A,COUNTA(A:A)) is simpler. For the last number specifically, swap the value range for the numeric column.

Variations

Simple version, no gaps

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

Returns the value in the last filled row when there are no blank gaps.

Last value from another column

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

Finds the last filled row in A but returns B — e.g. the amount for the latest date.

Last number only

=LOOKUP(9^99,A2:A)

Returns the last numeric value; 9^99 is larger than any realistic number.

Examples

Scenario	Formula
Most recent balance	`=LOOKUP(2,1/(A2:A<>""),A2:A)`
Amount for the latest entry	`=LOOKUP(2,1/(A2:A<>""),B2:B)`

FAQ

How do I get the last value in a column?

Use =LOOKUP(2,1/(A2:A<>""),A2:A). It returns the last non-empty cell even if there are blank rows above it.

Is there a simpler formula if there are no gaps?

Yes: =INDEX(A:A,COUNTA(A:A)) returns the last filled row when the data is contiguous.

How do I get the last number specifically?

Use =LOOKUP(9^99,A2:A) — it seeks a value larger than any number, so it lands on the last numeric entry.

Related formulas

→ See all 34 Google Sheets formulas in one cheat sheet