# How to get the last value in a row in Excel

Search the row from the right for a non-empty cell: `=XLOOKUP(TRUE,B2:F2<>"",B2:F2,,0,-1)`, filled down.

**Excel:** `=XLOOKUP(TRUE,B2:F2<>"",B2:F2,,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 | E | F | G |
| --- | --- | --- | --- | --- | --- | --- |
| Student | Sep | Oct | Nov | Dec | Jan | Latest score |
| Ava | 78 | 82 | 85 |  |  |  |
| Ben | 64 | 70 |  | 73 |  |  |
| Chloe | 91 | 88 | 94 | 96 | 93 |  |
| Dan | 55 |  |  |  |  |  |
| Eve | 72 | 75 | 79 | 81 |  |  |

Formula in G2, filled to G6: `=XLOOKUP(TRUE,B2:F2<>"",B2:F2,,0,-1)`

Result: 85; 73; 93; 55; 81

## How it works

`B2:F2<>""` marks each month that has a score. With search mode `-1`, XLOOKUP reads that row from right to left and returns the first score it reaches, so Ben gets 73 even though November is empty.

## Other ways

- `=INDEX(B2:F2,MAX((B2:F2<>"")*(COLUMN(B2:F2)-COLUMN(B2)+1)))`: Every Excel version (Ctrl+Shift+Enter in 2019 and earlier): MAX finds the position of the last filled cell in the row.

## Common mistakes

- Leaving out the `-1` returns the first score in the row instead of the last.
- Counting the filled cells, like `INDEX(B2:F2,COUNTA(B2:F2))`, breaks when a month in the middle is empty, as Ben's is.

## Questions

**How do I return the month of the latest score?**

Return the header row instead: `=XLOOKUP(TRUE,B2:F2<>"",B$1:F$1,,0,-1)`.

**How do I get the change from the first to the last score?**

Subtract the first one: `=XLOOKUP(TRUE,B2:F2<>"",B2:F2,,0,-1)-XLOOKUP(TRUE,B2:F2<>"",B2:F2)`.

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/last-value-in-row