Spreadsheet Practice

How to get the last value in a row in Excel

Formula (Excel and Google Sheets)

=XLOOKUP(TRUE,B2:F2<>"",B2:F2,,0,-1)

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

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

See it work

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

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.

=XLOOKUP(TRUE,B2:F2<>"",B2:F2,,0,-1)

Result85

  1. B2:F21 row × 5 cols: 78, 82, 85, (empty), …

    A range: 5 cells in one row.

  2. B2:F2<>""{1 row × 5 cols} TRUE, TRUE, TRUE, FALSE, …

    The <> operator tests whether they differ.

  3. -1-1

    Makes the value negative.

  4. XLOOKUP(TRUE,B2:F2<>"",B2:F2,,0,-1)85

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

    • lookup_valueTRUE
    • lookup_arrayB2:F2<>""{1 row × 5 cols} TRUE, TRUE, TRUE, FALSE, …
    • return_arrayB2:F21 row × 5 cols: 78, 82, 85, (empty), …
    • if_not_found(omitted)
    • match_mode0
    • search_mode-1-1

In Google Sheets

The same formula works in Google Sheets: =XLOOKUP(TRUE,B2:F2<>"",B2:F2,,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
=INDEX(B2:F2,MAX((B2:F2<>"")*(COLUMN(B2:F2)-COLUMN(B2)+1)))85, 73, 93…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.

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

In G2, return the latest score for the student in that row (the rightmost filled cell in B2:F2), then fill down to G6.

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

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.