Cellwise Try it free

Excel guide · 3 min read

How to find the last value in a column in Excel

Quick answerFor the last entry for a specific item, use XLOOKUP with search_mode -1, which searches from the bottom up: =XLOOKUP(G1,B2:B9,C2:C9,,0,-1). For the last value in a column with no gaps, use =INDEX(C2:C9,COUNTA(C2:C9)). When the column has blanks, use =INDEX(D2:D9,MATCH(2,1/(D2:D9<>""))).

Sample data

Every result on this page was calculated from this sheet by the same spreadsheet engine the Cellwise app uses.

ABCDEFG
1DateCustomerAmountMeterCustomerLumen Dental
2Sep 2, 2026Lumen Dental42010512
3Sep 5, 2026Oakridge Cafe18010588
4Sep 9, 2026Lumen Dental39510640
5Sep 12, 2026Pinecrest Vets760
6Sep 16, 2026Oakridge Cafe21010731
7Sep 19, 2026Lumen Dental450
8Sep 23, 2026Pinecrest Vets71510802
9Sep 26, 2026Oakridge Cafe195

Last match for an item: XLOOKUP from the bottom

A normal lookup returns the first match, which in a log sorted oldest to newest is the oldest payment. XLOOKUP's sixth argument, search_mode, set to -1 searches last to first, so the first hit it finds is the most recent.

Leave the fourth argument empty (or put a message there) and use 0 for exact match. XLOOKUP is in Excel 365 and Excel 2021 and later.

=XLOOKUP(G1,B2:B9,C2:C9,,0,-1)

Result: 450

450, Lumen Dental's latest payment on 2026-09-19. A plain XLOOKUP would return the first one, 420.

The date of the last match

Return a different column to answer "when did they last pay". Add a not-found message so a new customer shows something readable instead of #N/A.

This relies on the log being in date order. If rows are entered out of order, the bottom match is not the newest. Then use =MAXIFS(A2:A9,B2:B9,G1) on real dates instead.

=XLOOKUP(G1,B2:B9,A2:A9,"No payments",0,-1)

Result: Sep 19, 2026

Sep 19, 2026 is the last time Lumen Dental paid.

Last value in a column with no gaps: INDEX and COUNTA

COUNTA counts the filled cells, which in a column without blanks is also the position of the last one. INDEX returns the cell at that position. It works in every version of Excel.

Only use this when the column never has gaps. One empty cell above the end makes COUNTA one short, and you get the second-to-last value without any warning.

=INDEX(C2:C9,COUNTA(C2:C9))

Result: 195

195, the newest amount (Oakridge Cafe, 2026-09-26).

Last non-blank cell when there are gaps

The meter readings in column D skip some visits. (D2:D9<>"") gives TRUE or FALSE per row; 1 divided by that is 1 for filled rows and #DIV/0! for empty ones. MATCH looking for 2 in that list cannot find it, so it settles on the last 1, which is the last filled row.

In Excel 365 this works as typed. In Excel 2019 and older, confirm it with Ctrl+Shift+Enter. A clearer Excel 365 version is =XLOOKUP(TRUE,D2:D9<>"",D2:D9,,0,-1).

=INDEX(D2:D9,MATCH(2,1/(D2:D9<>"")))

Result: 10802

10,802, the reading on 2026-09-23. Rows 7 and 9 have no reading and are skipped.

Older Excel: LOOKUP and the big-number trick

The classic one-liner is =LOOKUP(2,1/(D2:D9<>""),D2:D9). It uses the same 1/condition idea, and LOOKUP handles arrays without Ctrl+Shift+Enter in every version. Add a condition to get the last match: =LOOKUP(2,1/(B2:B9="Lumen Dental"),C2:C9). The Cellwise practice engine does not run LOOKUP, so try these in Excel.

For a column of numbers only, =INDEX(D2:D9,MATCH(9.99E+307,D2:D9)) looks for a number bigger than any real one, and approximate MATCH lands on the last number in the column.

=INDEX(D2:D9,MATCH(9.99E+307,D2:D9))

Result: 10802

10,802 again: the last number in column D.

Common mistakes

Using VLOOKUP and getting the oldest entry

VLOOKUP, MATCH with 0 and a default XLOOKUP all stop at the first match from the top. Use XLOOKUP with -1 as the sixth argument, or LOOKUP(2,1/(...)).

INDEX and COUNTA on a column with gaps

Each blank above the end shifts the result up one row. Use the MATCH(2,1/(...)) version, which ignores gaps.

Formulas returning "" count as filled

COUNTA counts cells with formulas, even when they show nothing. Test with <>"" (as in the MATCH version) so empty-looking formula cells are skipped.

Whole-column references that are slow

MATCH(2,1/(D:D<>"")) checks over a million rows each time the sheet recalculates. Use a fixed range a little bigger than your data, or an Excel table column.

FAQ

How do I find the last row with data in Excel?

Use =MATCH(2,1/(A2:A1000<>"")) to get its position inside the range, and add 1 for the sheet row if the range starts on row 2. To jump there by hand, click in the column and press Ctrl+Down Arrow.

How do I get the most recent date for a customer?

If the dates are real Excel dates, =MAXIFS(A2:A9,B2:B9,"Lumen Dental") returns the latest one no matter how the rows are sorted. MAXIFS is in Excel 2019 and later.

What does search_mode -1 do in XLOOKUP?

It searches from the last item to the first, so XLOOKUP returns the bottom-most match. 1 (the default) searches top to bottom; 2 and -2 do fast binary searches on sorted data.

How do I get the last value in a row instead of a column?

The same formulas work sideways. For row 2 from B to M: =INDEX(B2:M2,MATCH(2,1/(B2:M2<>""))) returns the right-most filled cell.

Functions used

Related guides

Updated