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

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.

**Excel:** `=XLOOKUP(A2,A$1:A1,C$1:C1,"",0,-1)` (Excel 2021, 2024 and Microsoft 365)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C | D |
| --- | --- | --- | --- |
| Meter | Date | Reading | Previous reading |
| M-1 | 2026-01-31 | 10450 |  |
| M-2 | 2026-01-31 | 8820 |  |
| M-3 | 2026-01-31 | 15210 |  |
| M-1 | 2026-02-28 | 10910 |  |
| M-2 | 2026-02-28 | 9245 |  |
| M-1 | 2026-03-31 | 11398 |  |
| M-3 | 2026-03-31 | 16004 |  |
| M-2 | 2026-03-31 | 9690 |  |

Formula in D2, filled to D9: `=XLOOKUP(A2,A$1:A1,C$1:C1,"",0,-1)`

Result: ; ; ; 10,450; 8,820; 10,910; 15,210; 9,245

## 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 "".

## Other ways

- `=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.

## 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)`.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/previous-value-for-same-item