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

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

**Excel:** `=INDEX(A2:A7,MOD(MATCH(D1,A2:A7,0),ROWS(A2:A7))+1)` (Every current version)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C | D |
| --- | --- | --- | --- |
| Rotation |  | Current | Priya |
| Marcus |  | Next up |  |
| Priya |  |  |  |
| Tom |  |  |  |
| Rosa |  |  |  |
| Ken |  |  |  |
| Aisha |  |  |  |

Formula in D2: `=INDEX(A2:A7,MOD(MATCH(D1,A2:A7,0),ROWS(A2:A7))+1)`

Result: Tom

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

## Other ways

- `=XLOOKUP(D1,A2:A7,VSTACK(A3:A7,A2))`: Pairs 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.

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

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/next-item-in-list-wraparound