OFFSET vs INDEX in Excel: Which Should You Use?

OFFSET vs INDEX comes down to one word: volatile. Both functions return a cell or a range by its position, and most OFFSET formulas can be rewritten with INDEX. But OFFSET recalculates after every edit anywhere in the workbook, while INDEX waits until its own inputs change. This comparison shows both on the same data so you can see when each one fits, and why INDEX is the safer default.

OFFSET vs INDEX at a glance#

The two functions answer the same question in different ways. OFFSET says "start here and move". INDEX says "take this range and pick a position".

What OFFSET does#

OFFSET starts at a reference, moves down a number of rows and across a number of columns, and returns the cell it lands on. Two optional arguments, height and width, make it return a block of cells instead of one.

=OFFSET(B1,3,0)

This starts at B1, moves 3 rows down and 0 columns across, and returns B4. Add a height and the result becomes a range: =SUM(OFFSET(B1,1,0,3,1)) adds B2:B4, three rows tall and one column wide.

Negative numbers move up or left. If the move goes off the edge of the sheet, OFFSET returns #REF!. The OFFSET function guide explains every argument in detail.

What INDEX does#

INDEX takes a range and returns the value at a given row (and column) inside it. Position 1 is the first cell of the range, not row 1 of the sheet.

=INDEX(B2:B8,3)

This returns the third cell of B2:B8, which is B4. With a two-dimensional range, add a column number: =INDEX(A2:D8,3,2) returns row 3, column 2 of that block.

INDEX returns a reference, not just a value. That means you can put a colon between two INDEX results to build a range, which is how INDEX copies OFFSET's height and width. The INDEX function reference and the article on the INDEX function in Excel cover the other forms.

The same tasks with OFFSET and INDEX#

Here's the same monthly sales data for every example. Sales are filled in for January to May; June and July are still empty.

Every pair gives the same answer. The last row is the interesting one. COUNT finds 5 filled months, so OFFSET starts 3 rows below B1 (at B4) and takes 3 rows. INDEX builds B4:B6 from position 3 to position 5. Both add 5,100 + 4,800 + 5,600 = 15,500. When June's number arrives, both totals move forward a month on their own.

The INDEX version is longer. That's the main reason people still write OFFSET. Its benefits only show up in a big file.

Note that COUNT assumes there are no gaps. If a month in the middle is empty, both versions count one month short.

When to use OFFSET#

OFFSET is fine when the workbook is small and the formula is a one-off. It is also easier to read when you really do think in "move from here" terms, such as "the cell 12 rows above this one".

Reasonable uses:

• A quick calculation in a small personal file, where speed doesn't matter.
• An existing model that already uses OFFSET everywhere and works. Rewriting it adds risk.
• Dynamic named ranges in older Excel, for charts or drop-down lists. Even here, an Excel table is now the better choice, as the guide to drop-down lists that update themselves shows.

When to use INDEX#

Use INDEX for lookups, dynamic ranges and anything in a shared or large workbook. It recalculates only when its inputs change, so it doesn't slow the file down.

INDEX also behaves better when the sheet changes:

• Inserted rows. If you insert a row inside B2:B8, Excel stretches the range to B2:B9 and INDEX still counts correctly. OFFSET counts rows from B1, so inserted rows can shift which cell it lands on.
• Auditing. Select an INDEX formula and use Formulas > Trace Precedents: the arrow points to the whole range. With OFFSET, Excel only shows the starting cell, not the cells it actually returns.
• Dynamic named ranges. The classic OFFSET range for a growing list is =OFFSET($A$2,0,0,COUNTA($A:$A)-1,1): start at A2, take as many rows as there are filled cells below the header. The INDEX version is =$A$2:INDEX($A:$A,COUNTA($A:$A)): from A2 down to the last filled row. Both feed a chart or a drop-down list in the same way, but only the INDEX version stays quiet while you work elsewhere in the file. Both assume the column has no blank cells in the middle.
• Lookups. INDEX with MATCH is the classic lookup that can look left. The guide to INDEX MATCH walks through it, and VLOOKUP vs INDEX MATCH explains why many analysts prefer it.

Common mistakes with OFFSET and INDEX#

Most problems come from counting positions wrongly.

1. Counting from the wrong starting point. OFFSET(B1,1,0) returns B2: the move starts at B1. INDEX(B2:B8,1) also returns B2: position 1 is the first cell of the range. Mixing up the two systems gives answers one row off.
2. Forgetting that OFFSET is volatile. Hundreds of OFFSET formulas, or OFFSET inside SUMIFS on long ranges, can make every edit lag. See why Excel formulas get slow for the other usual causes.
3. Using a match type other than 0. MATCH("Apr",A2:A8) without the 0 assumes sorted data and can return the wrong month. Always add the 0 for an exact match. The MATCH function reference explains the three match types.
4. Gaps in the data. COUNT-based "last value" formulas break when a cell in the middle is blank. Fill gaps with 0 or use a different method.
5. Returning a range to a single cell. =OFFSET(B1,1,0,3,1) on its own spills three values in Microsoft 365, and may show #VALUE! in older Excel. Wrap it in SUM, AVERAGE or MAX.

Our recommendation#

Default to INDEX. It does everything OFFSET does, it doesn't slow your workbook, and it's easier for the next person to check. Use INDEX with MATCH for lookups, and INDEX(...):INDEX(...) when you need a moving range such as "the last 3 months".

Keep OFFSET for small, personal files where the shorter formula reads better, or for existing models you don't want to rewrite. If a job test asks you to fix a slow workbook, replacing OFFSET with INDEX is a strong answer.

Practise it#

INDEX only becomes natural after you've written it a few times. Practise INDEX and MATCH in a live spreadsheet: you type the formula and it checks the answer instantly. Then try the exercise on this page, where you turn an OFFSET lookup into INDEX MATCH.