Return the last filled value in column A, which grows as new rows are added

公式

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

说明

1/(A:A<>"") is 1 on filled cells and #DIV/0! on blanks. LOOKUP(2, …) walks to the last numeric 1 and returns the matching value — including text.

步骤

  1. A:A is the column to scan.
  2. Filled cells become 1, blanks become errors.
  3. LOOKUP ignores errors and takes the last 1.

变体

Excel 365 TAKE

TOCOL with 1 drops blanks; TAKE(…, -1) is the last item.

=TAKE(TOCOL(A:A, 1), -1)

Last numeric value only

Skips text labels at the bottom of the column.

=LOOKUP(2, 1/(ISNUMBER(A:A)), A:A)

Last row number

Use with INDEX to return a whole record.

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