Find the last entry in a log column in Excel with LOOKUP

The reading that matters in a meter log is the last one, and the last one keeps moving as the log grows. One formula finds the bottom of the column — no fixed row, no counting rows by hand.

Written 12 November 2026

1What you need

• Excel, Google Sheets, or LibreOffice. LOOKUP and COUNT work the same in all three for this tutorial.
• The file above, or a blank workbook if you want to type the formulas in yourself.

2The log

A meter log grows downward: one row a day, newest at the bottom. This one covers 2 to 13 November 2026, and two days were missed — the 8th and the 12th have no reading:

The last entry is the one that matters — usage bills from the latest reading — and it is not in a fixed row, because next week the log will be longer. The formula has to find the bottom of the column wherever it currently is.

3The last reading

E2  =LOOKUP(10^307,B3:B14)

LOOKUP, in its one-argument form, searches a column for a value and returns the matching entry. When it cannot find the value, it matches the largest value in the column that is less than or equal to the one asked for (LOOKUP function — Microsoft Support). The first argument, 10^307 — a 1 followed by 307 zeros — is far larger than any reading a meter will ever show, so no reading can equal it — and the match lands on the largest reading at or below it, which, in a column that only rises, is the last one: 1598. The two blank days do not disturb the result; a blank holds no number to match.

4The last date

Dates are stored as numbers — each day is one more than the day before — so the same formula over the date column returns the date of the last entry:

E3  =LOOKUP(10^307,A3:A14)

Formatted as a date, the result is 13 November 2026. A last-reading figure with its date beside it can be quoted without opening the log.

5Usage over the period

E4  =B3
E5  =E2-E4
E6  =COUNT(B3:B14)

The first reading, 1240, is pulled into E4 so the usage formula reads plainly: last minus first is 358 kWh. COUNT returns 10 — the number of readings actually taken, out of twelve days. COUNT counts numbers only, which is what makes it the right way to count a log with blanks in it.

6When the log grows

New days go at the bottom, and the ranges have to grow with them: a reading added in row 15 is outside B3:B14, and every formula keeps answering for the old log. Extend the ranges to the new bottom row — or size them generously from the start, with room below the data. The one thing not to do is leave the formulas pointing at a range the log has outgrown; the answers stay plausible while quietly going stale.

Traps

Sources

• LOOKUP function — Microsoft Support — the vector form, the ascending-order requirement, matching the largest value less than or equal to the lookup value, and the #N/A result when the lookup value is smaller than the smallest value in the column.