INDEX Function in Excel
INDEX returns a value from a range by its position — pick the third item in a column, the value at row 5 column 2 of a table, or the whole 4th column of a grid. It's usually paired with MATCH, but understanding it standalone unlocks patterns most users miss.
What it does: Returns the value at the specified position in array. For a single row or column, one position argument is enough. For a 2D range, provide both row and column positions.
What INDEX does
INDEX is the "give me the value at position N" function. In its simplest form, =INDEX(A1:A10, 3) returns the third value from that column. For a 2D range, =INDEX(A1:E10, 3, 2) returns the value at row 3, column 2 of the range.
What makes INDEX special isn't just position lookup — it's that INDEX returns an actual reference, not just a value. This means you can use it inside SUM, on the left of a colon to build dynamic ranges, or wherever Excel expects a range.
=SUM(A2:INDEX(A:A, 100)) sums from A2 to whatever row 100 is — a dynamic range with INDEX as the end anchor. Change 100 to a formula and the range grows or shrinks. This is the classic pre-dynamic-array way to build resizing ranges without volatile OFFSET.
Syntax breakdown
array Required
The range or array to look inside. Can be a single row, single column, 2D rectangular range, or an array constant. Named ranges and Table columns work too.
row_num Required (usually)
The row position within array, starting at 1. Pass 0 or leave blank to return the entire column (when column_num is provided). Must be within the array's bounds.
column_num Optional
The column position within array, starting at 1. For a single-column array, omit this. For 2D ranges, provide it. Pass 0 to return the entire row (when row_num is provided).
5 real-world examples
Example 1: Get the Nth value from a column
Column A has customer names. Get the 5th name:
=INDEX(A2:A100, 5)Result: The value in the 5th position of A2:A100 (which is A6). Position 1 is A2, position 2 is A3, and so on — INDEX is relative to the range, not the sheet.
Example 2: The classic INDEX/MATCH combination
Look up email for a customer ID:
=INDEX(C:C, MATCH(A2, B:B, 0))Result: Email for the customer whose ID matches A2. MATCH finds the position, INDEX returns the value at that position. Works when the return column is left, right, or anywhere.
Example 3: Return an entire column from a grid
Grid B2:F11 has monthly data. Return the whole "March" column (column 3 of the range):
=INDEX(B2:F11, 0, 3)Result: All ten values of column 3, spilled into cells. Pass 0 (or leave blank) for row_num to select the whole column. Excel 365 spills the result automatically.
Example 4: Dynamic range with INDEX as end anchor
Sum the first N rows of column A, where N is stored in D1:
=SUM(A2:INDEX(A:A, D1+1))Result: Sum from A2 to row (D1+1). INDEX returns a reference, so it can be used as the end of a range. Non-volatile, unlike OFFSET.
Example 5: Two-way lookup with two MATCHes
Cross-tab lookup: find sales for region "East" in month "March":
=INDEX(B2:M11, MATCH("East", A2:A11, 0), MATCH("March", B1:M1, 0))Result: The cell at that intersection. Both MATCHes find their positions independently, then INDEX pulls the value. Classic pre-XLOOKUP two-way lookup.
Common errors and how to fix them
The row or column number is out of range. If the array has 100 rows and you asked for row 101, INDEX returns #REF!. Check bounds with COUNTA or ROWS: =INDEX(A:A, MIN(target, COUNTA(A:A))).
Non-numeric row or column number, or a 2D array with only one position argument that's ambiguous. Provide both row and column when the array is 2D — even if one is 0.
Not INDEX's fault — the wrapping MATCH failed to find the value. Fix at the source: =IFERROR(INDEX(return, MATCH(lookup, range, 0)), "Not found").
Usually a position off-by-one. INDEX is 1-indexed relative to the start of the array. If your array starts at A2 and you want cell A5, that's position 4 (not 5). Draw out the positions mentally.
INDEX vs alternatives
| Function | Best for | Trade-off |
|---|---|---|
| INDEX | Value at known position; dynamic ranges | Position-based, not value-based |
| INDEX/MATCH | Any-direction value lookup | Two functions in one formula |
| XLOOKUP | Modern lookups; simpler syntax | Excel 2021+ only |
| CHOOSE | Fixed list of ≤5 items | Values must be listed explicitly |
| OFFSET | Shifted references from a starting point | Volatile — recalculates constantly |
| TAKE / DROP | First/last N rows or columns | Excel 365 only |
Version compatibility
Download the practice workbook
Every example above, plus dynamic-range patterns and INDEX + MATCH cross-tab templates.
Related functions
Frequently asked questions
Is INDEX the same as INDEX/MATCH?
No. INDEX by itself takes a position (a number) and returns the value there. INDEX/MATCH is a pattern where MATCH finds the position and passes it to INDEX. INDEX is the tool; INDEX/MATCH is the workflow.
Why does INDEX return a reference and not just a value?
Because INDEX evaluates to a real cell reference internally. This means it can appear on either side of a colon to build ranges (A2:INDEX(A:A, 10)), inside SUM/AVERAGE (where a range is needed), or anywhere Excel expects an address. Most functions return only values — INDEX returns something more powerful.
Can INDEX return an entire row or column?
Yes. Pass 0 or leave blank for the dimension you want the entire row/column of. =INDEX(range, 3, 0) returns all of row 3; =INDEX(range, 0, 2) returns all of column 2. In Excel 365, these spill into adjacent cells.
Is INDEX volatile like OFFSET?
No — INDEX is not volatile. It only recalculates when its inputs change, unlike OFFSET which recalculates on every workbook change. This is a major reason to prefer INDEX for dynamic ranges over OFFSET in performance-sensitive workbooks.
Can I use INDEX with a Table (structured reference)?
Yes: =INDEX(Sales[Amount], 3) returns the 3rd amount. INDEX plays well with Table columns. For row-based access inside Tables, MATCH with the whole Table works too: =INDEX(Sales[Amount], MATCH(id, Sales[ID], 0)).
Which is faster — INDEX/MATCH or XLOOKUP?
Very close in practice, with slight edges depending on data. INDEX/MATCH has decades of optimization. XLOOKUP is newer but well-optimized. For most workbooks the difference is imperceptible. Choose based on syntax clarity, not speed.
Can INDEX return multiple values at once?
Yes with dynamic arrays. =INDEX(A2:A100, {1;5;10}) returns positions 1, 5, and 10 as a spilled array. Works in Excel 365 / 2021+. In older Excel, wrap in an array formula.
Build INDEX-based lookups and dynamic ranges — with the Sheets & Cells AI Add-in
Point the Add-in at your data and describe what you need — "3rd row of March column" or "sum up to variable end row" — and it writes the INDEX formula with correct positions. All inside Excel.
Learn about the Add-in →