Spreadsheet Practice

How to get the previous value for the same item in Excel

Formula (Excel and Google Sheets)

=XLOOKUP(A2,A$1:A1,C$1:C1,"",0,-1)

Look up the item in the rows above only, searching from the bottom: =XLOOKUP(A2,A$1:A1,C$1:C1,"",0,-1), filled down.

Result on the example
one result per row in D2, filled down to D9
Works in
Excel 2021, 2024 and Microsoft 365
Google Sheets
Same formula

See it work

The formula is already in D2, filled down to D9. Change any input and the result updates, or edit the formula to experiment.

How it works

A$1:A1 grows as the formula fills down: in row 7 it is A$1:A6, every row above. XLOOKUP searches those rows from the bottom (-1), so it finds the meter's most recent earlier reading. A meter's first row finds nothing and shows "".

=XLOOKUP(A2,A$1:A1,C$1:C1,"",0,-1)

Result""

  1. A2"M-1"

    The value in A2.

  2. A$1:A1"Meter"

    A range: one cell.

  3. C$1:C1"Reading"

    A range: one cell.

  4. -1-1

    Makes the value negative.

  5. XLOOKUP(A2,A$1:A1,C$1:C1,"",0,-1)""

    XLOOKUP: Searches one range and returns the matching item from another.

    • lookup_valueA2"M-1"
    • lookup_arrayA$1:A1"Meter"
    • return_arrayC$1:C1"Reading"
    • if_not_found""
    • match_mode0
    • search_mode-1-1

In Google Sheets

The same formula works in Google Sheets: =XLOOKUP(A2,A$1:A1,C$1:C1,"",0,-1). We ran it in Google Sheets mode on the same data and it returns the same results.

Other ways to do it

Each one was run on the same example sheet.

FormulaResultWhen to use it
=IFERROR(INDEX(C$1:C1,MATCH(2,1/(A$1:A1=A2))),""), , …Every Excel version: MATCH(2,1/…) finds the last 1, the last matching row above. Confirm with Ctrl+Shift+Enter in Excel 2019 and earlier.

Common mistakes

  • The range must stop on the row above. A$1:A2 in row 2 would find the row itself.
  • The log must be in date order, because "previous" here means the nearest row above.

Try it yourself

Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.

Get the previous value for the same item

The log mixes three meters in date order. In D2, return the previous reading of the same meter (blank for its first reading), then fill down to D9.

Questions

How do I calculate the usage since the last reading?

Subtract when there is a previous reading: =IF(D2="","",C2-D2).

How do I get the previous date instead?

Return the date column: =XLOOKUP(A2,A$1:A1,B$1:B1,"",0,-1).

Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.