Spreadsheet Practice

How to return the next item in a list after a given value

Formula (Excel and Google Sheets)

=INDEX(A2:A7,MOD(MATCH(D1,A2:A7,0),ROWS(A2:A7))+1)

Find the value's position, add one and wrap around at the end: =INDEX(A2:A7,MOD(MATCH(D1,A2:A7,0),ROWS(A2:A7))+1).

Result on the example
Tom in D2
Works in
Every current version
Google Sheets
Same formula

See it work

The formula is already in D2. Change any input and the result updates, or edit the formula to experiment.

How it works

MATCH gives Priya's position, 2. The next person is at position 3, but after Aisha (6) it must go back to 1. MOD(position,6)+1 does both: MOD(2,6)+1 is 3, and MOD(6,6)+1 is 1. ROWS supplies the 6 so the list can grow.

=INDEX(A2:A7,MOD(MATCH(D1,A2:A7,0),ROWS(A2:A7))+1)

Result"Tom"

  1. A2:A76 rows × 1 col: "Marcus", "Priya", "Tom", "Rosa", …

    A range: 6 cells in one column.

  2. D1"Priya"

    The value in D1.

  3. MATCH(D1,A2:A7,0)2

    MATCH: Position of a value in a list. 0 = exact match.

    • lookup_valueD1"Priya"
    • lookup_arrayA2:A76 rows × 1 col: "Marcus", "Priya", "Tom", "Rosa", …
    • match_type0
  4. ROWS(A2:A7)6

    ROWS: Number of rows in a range.

    • arrayA2:A76 rows × 1 col: "Marcus", "Priya", "Tom", "Rosa", …
  5. MOD(MATCH(D1,A2:A7,0),ROWS(A2:A7))2

    MOD: Remainder after division.

    • numberMATCH(D1,A2:A7,0)2
    • divisorROWS(A2:A7)6
  6. MOD(MATCH(D1,A2:A7,0),ROWS(A2:A7))+13

    The + operator adds.

  7. INDEX(A2:A7,MOD(MATCH(D1,A2:A7,0),ROWS(A2:A7))+1)"Tom"

    INDEX: Value at a given row and column of a range.

    • arrayA2:A76 rows × 1 col: "Marcus", "Priya", "Tom", "Rosa", …
    • row_numMOD(MATCH(D1,A2:A7,0),ROWS(A2:A7))+13

In Google Sheets

The same formula works in Google Sheets: =INDEX(A2:A7,MOD(MATCH(D1,A2:A7,0),ROWS(A2:A7))+1). We ran it in Google Sheets mode on the same data and it returns Tom.

Other ways to do it

Each one was run on the same example sheet.

FormulaResultWhen to use it
=XLOOKUP(D1,A2:A7,VSTACK(A3:A7,A2))TomPairs each name with the list shifted up by one, with the first name moved to the end. Excel 2024 and Microsoft 365.

Common mistakes

  • Without MOD, the last person returns #REF! because there is no next row.
  • If a name appears twice in the rotation, MATCH always starts from the first one.

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.

Return the next item in a list after a given value

In D2, return the person after the one in D1 in the rotation. After the last person, it goes back to the first.

Questions

How do I get the previous person?

Step back instead: =INDEX(A2:A7,MOD(MATCH(D1,A2:A7,0)-2,ROWS(A2:A7))+1).

How do I work out who is on for a given date?

Count the weeks since the rotation started; see who is on duty for a date in a rotation.

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.