# How to get the latest value in a row for a matching name

Look up the person's row, then take its last filled cell: `=LET(vals,XLOOKUP(J1,A2:A6,B2:G6),XLOOKUP(TRUE,vals<>"",vals,,0,-1))`.

**Excel:** `=LET(vals,XLOOKUP(J1,A2:A6,B2:G6),XLOOKUP(TRUE,vals<>"",vals,,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 | H | I | J |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
| Member | Wk 1 | Wk 2 | Wk 3 | Wk 4 | Wk 5 | Wk 6 |  | Member | Jordan |
| Alex | 182 | 180 | 179 |  | 177 |  |  | Latest weigh-in |  |
| Jordan | 205 | 203 |  | 199 |  |  |  |  |  |
| Sam | 164 |  | 163 | 161 | 160 | 158 |  |  |  |
| Riley | 221 | 218 | 216 |  |  |  |  |  |  |
| Casey | 148 | 147 | 147 | 146 |  | 145 |  |  |  |

Formula in J2: `=LET(vals,XLOOKUP(J1,A2:A6,B2:G6),XLOOKUP(TRUE,vals<>"",vals,,0,-1))`

Result: 199

## How it works

The inner XLOOKUP returns Jordan's six weekly cells, and LET calls them `vals`. The second XLOOKUP searches that row from the right (`-1`) for the first cell that isn't empty, so the blanks in weeks 5 and 6 are skipped and the result is week 4's 199.

## Other ways

- `=INDEX(B2:G6,MATCH(J1,A2:A6,0),MAX((INDEX(B2:G6,MATCH(J1,A2:A6,0),0)<>"")*(COLUMN(B2:G2)-COLUMN(B2)+1)))`: For Excel 2019 and earlier (Ctrl+Shift+Enter): MAX finds the last filled column in the member's row.

## Common mistakes

- Without the `-1`, the formula returns the first weigh-in instead of the latest.
- A member with no entries returns #N/A. Add a 4th argument to the second XLOOKUP for a message.

## Questions

**How do I return the week of the latest entry?**

Return the header row: `=LET(vals,XLOOKUP(J1,A2:A6,B2:G6),XLOOKUP(TRUE,vals<>"",B1:G1,,0,-1))`.

**How do I get the change since the first entry?**

Subtract the first filled value: `XLOOKUP(TRUE,vals<>"",vals)` inside the same LET.

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/latest-value-in-row-for-name