How to get the latest value in a row for a matching name
Formula (Excel and Google Sheets)
=LET(vals,XLOOKUP(J1,A2:A6,B2:G6),XLOOKUP(TRUE,vals<>"",vals,,0,-1))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)).
- Result on the example
- 199 in
J2 - Works in
- Excel 2021, 2024 and Microsoft 365
- Google Sheets
- Same formula
See it work
The formula is already in J2. Change any input and the result updates, or edit the formula to experiment.
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.
=LET(vals,XLOOKUP(J1,A2:A6,B2:G6),XLOOKUP(TRUE,vals<>"",vals,,0,-1))Result199
vals{1 row × 6 cols} 205, 203, (empty), 199, …A name defined in LET, or a named range.
J1"Jordan"The value in J1.
A2:A65 rows × 1 col: "Alex", "Jordan", "Sam", "Riley", …A range: 5 cells in one column.
B2:G65 rows × 6 cols: 182, 180, 179, (empty), …A range: 5 rows × 6 columns.
XLOOKUP(J1,A2:A6,B2:G6){1 row × 6 cols} 205, 203, (empty), 199, …XLOOKUP: Searches one range and returns the matching item from another.- lookup_value
J1"Jordan" - lookup_array
A2:A65 rows × 1 col: "Alex", "Jordan", "Sam", "Riley", … - return_array
B2:G65 rows × 6 cols: 182, 180, 179, (empty), …
- lookup_value
vals<>""{1 row × 6 cols} TRUE, TRUE, FALSE, TRUE, …The <> operator tests whether they differ.
-1-1Makes the value negative.
XLOOKUP(TRUE,vals<>"",vals,,0,-1)199XLOOKUP: Searches one range and returns the matching item from another.- lookup_value
TRUE - lookup_array
vals<>""{1 row × 6 cols} TRUE, TRUE, FALSE, TRUE, … - return_array
vals{1 row × 6 cols} 205, 203, (empty), 199, … - if_not_found
(omitted) - match_mode
0 - search_mode
-1-1
- lookup_value
LET(vals,XLOOKUP(J1,A2:A6,B2:G6),XLOOKUP(TRUE,vals<>"",vals,,0,-1))199LET: Names values inside a formula.- name1
vals{1 row × 6 cols} 205, 203, (empty), 199, … - value1
XLOOKUP(J1,A2:A6,B2:G6){1 row × 6 cols} 205, 203, (empty), 199, … - name2, value2
XLOOKUP(TRUE,vals<>"",vals,,0,-1)199
- name1
In Google Sheets
The same formula works in Google Sheets: =LET(vals,XLOOKUP(J1,A2:A6,B2:G6),XLOOKUP(TRUE,vals<>"",vals,,0,-1)). We ran it in Google Sheets mode on the same data and it returns 199.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =INDEX(B2:G6,MATCH(J1,A2:A6,0),MAX((INDEX(B2:G6,MATCH(J1,A2:A6,0),0)<>"")*(COLUMN(B2:G2)-COLUMN(B2)+1))) | 199 | 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.
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 latest value in a row for a matching name
J2, return the most recent weigh-in (the last filled cell in the row) for the member in J1.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.
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.