Excel INDEX Function
last modified October 5, 2026
The INDEX function is a powerful lookup function in Excel that
returns a value, or a reference to a value, from a specific position in a range
or array. It works with a single row, a single column, a two-dimensional table,
or even several non-adjacent areas. This tutorial provides a comprehensive guide
to using the INDEX function. You'll learn its syntax, the difference
between the array and reference forms, and practical applications through
detailed examples.
INDEX Function Basics
The INDEX function has two forms. The array form returns a value
from a range or an array constant, while the reference form returns a reference
to a cell or cells. The two forms share the same first three arguments, so the
formula looks almost identical in both cases.
| Component | Description |
|---|---|
| Function Name | INDEX |
| Syntax (Array Form) | =INDEX(array, row_num, [column_num]) |
| Syntax (Reference Form) | =INDEX(reference, row_num, [column_num], [area_num]) |
| Arguments | array or reference, row_num, column_num (optional), area_num (optional) |
| Return Value | A value, an array, or a reference at the specified position |
This table outlines the key components of the INDEX function. The
array form takes an array, a row number, and an optional column number. The
reference form adds an optional area number that selects one of several
non-adjacent ranges.
| Argument | Meaning |
|---|---|
| array | A range of cells or an array constant. If the array contains only one row or one column, the matching row_num or column_num argument is optional. |
| reference | One or more cell ranges. Non-adjacent areas must be enclosed in
parentheses, for example (A1:C3, E1:G3). |
| row_num | The row position from which to return a value or reference. Use 0 to return the whole column. |
| column_num | Optional. The column position from which to return a value or reference. Use 0 to return the whole row. |
| area_num | Optional. Selects an area inside a multi-area reference; the first area is 1. If omitted, area 1 is used. |
Only the first argument and one position argument are required. When the array
has more than one row and more than one column, supplying just row_num
returns the entire row, and supplying just column_num returns the
entire column.
Basic INDEX Example
This example demonstrates the simplest use of INDEX to retrieve a value from a single-column range.
| A | B |
|---|---|
| Apple | |
| Banana | |
| Cherry | |
| =INDEX(A1:A3, 2) |
The table shows a simple list of fruits in column A. The INDEX formula in B4 retrieves the second item from the range A1:A3.
=INDEX(A1:A3, 2)
This formula returns "Banana" as it's the second item in the range A1:A3. Note that we didn't need to specify a column number since we're working with a single column.
INDEX with Two-Dimensional Range
INDEX can retrieve values from two-dimensional ranges by specifying both row and column numbers. This example shows how to use it with a table.
| A | B | C |
|---|---|---|
| Name | Age | Department |
| John | 32 | Sales |
| Sarah | 28 | Marketing |
| =INDEX(A1:C3, 3, 2) |
This employee data table demonstrates INDEX working with both rows and columns. The formula retrieves a specific value by its position in the table.
=INDEX(A1:C3, 3, 2)
The formula returns 28, which is Sarah's age. It looks at row 3 (Sarah's row) and column 2 (Age column) in the range A1:C3. This shows INDEX's ability to work with tables.
INDEX with MATCH for Lookups
Combining INDEX with MATCH creates a powerful lookup alternative to VLOOKUP. This example demonstrates this common pattern.
| A | B | C | D |
|---|---|---|---|
| ID | Product | Price | |
| 101 | Laptop | 999 | |
| 102 | Phone | 699 | |
| 103 | Tablet | 399 | |
| =INDEX(B1:B4, MATCH(102, A1:A4, 0)) |
This product table shows how to find a product name by its ID using INDEX and MATCH together. The MATCH function finds the row number where ID 102 appears.
=INDEX(B1:B4, MATCH(102, A1:A4, 0))
This formula returns "Phone". MATCH finds ID 102 in row 3, then INDEX returns the product name from column B, row 3. This combination is more flexible than VLOOKUP.
INDEX with Row or Column 0
Setting row_num or column_num to 0 returns the entire
column or row instead of a single cell. This is the standard way to feed a whole
row or column of a table into another function such as SUM.
| A | B | C | D |
|---|---|---|---|
| Region | Q1 | Q2 | Q3 |
| North | 120 | 150 | 180 |
| South | 90 | 110 | 130 |
| =INDEX(A1:D3, 2, 0) |
This sales data table demonstrates how INDEX returns an entire row
when the column argument is 0. The formula retrieves all data for the North
region.
=INDEX(A1:D3, 2, 0)
This formula returns the array {"North", 120, 150, 180}, the entire second row.
In Excel 365 the values spill across four cells automatically; in older versions
the formula must be confirmed with Ctrl+Shift+Enter. Wrapped in
SUM, the same expression totals the row:
=SUM(INDEX(A1:D3, 2, 0)) returns 450. To return a whole column
instead, set row_num to 0, for example
=SUM(INDEX(A1:D3, 0, 3)), which totals the Q2 column and returns 260.
INDEX with Dynamic Arrays
In modern Excel, INDEX can work with dynamic arrays to create powerful formulas. This example shows INDEX filtering data.
| A | B | C |
|---|---|---|
| Product | Sales | |
| A | 100 | |
| B | 200 | |
| A | 150 | |
| =INDEX(FILTER(A1:B4, A1:A4="A"), , 2) |
This sales data shows how INDEX can extract specific columns from filtered results. The FILTER function first finds all rows where Product equals "A".
=INDEX(FILTER(A1:B4, A1:A4="A"), , 2)
This formula returns only the sales numbers (column 2) for product "A". The empty argument before the comma means "all rows" from the filtered results.
INDEX Reference Form
The reference form of INDEX returns a cell reference rather than a
value. That reference can be used anywhere Excel expects a range, which enables
dynamic ranges and constructs such as SUM(A1:INDEX(...)).
| A | B | C |
|---|---|---|
| Month | Sales | Cost |
| Jan | 100 | 40 |
| Feb | 200 | 80 |
| Mar | 300 | 120 |
The table holds monthly sales and costs. The following formulas use
INDEX to build ranges and references from that data.
=SUM(A2:INDEX(A1:C4, 4, 3)) ' 840 =SUM(INDEX(A1:C4, 0, 2)) ' 600 =INDEX((A2:B4, C2:C4), 3, 1, 2) ' 120
The first formula sums the range from A2 to the cell returned by
INDEX(A1:C4, 4, 3), which is C4, so it totals A2:C4 and returns 840.
The second formula uses row_num 0 to sum the whole second column,
B1:B4, returning 600. The third formula selects area 2, the range C2:C4, and
returns its third row, 120. Because INDEX hands back a reference,
SUM, CELL, and the range operator all accept its result
directly.
INDEX for Dynamic Ranges
Because the reference form returns a reference, INDEX can compute
the end of a range at run time. This creates dynamic ranges that grow or shrink
with the data instead of using a fixed address.
| A | B |
|---|---|
| 10 | =SUM(A1:INDEX(A1:A4, 2)) |
| 20 | |
| 30 | |
| 40 |
The table holds four numbers. The formula in B1 builds the range
A1:A2 by asking INDEX for the second cell of A1:A4.
=SUM(A1:INDEX(A1:A4, 2)) ' 30 =INDEX(A1:A4, COUNTA(A1:A4)) ' 40, the last value =SUM(A1:INDEX(A1:A4, COUNTA(A1:A4))) ' 100, the whole list
The first formula sums A1:A2 and returns 30. The second formula uses
COUNTA to count the populated cells and returns the last value, 40.
The third formula combines both ideas and sums the full list, 100. As data is
added or removed, these ranges adapt without editing the formulas.
INDEX Errors and Troubleshooting
| Cause | Symptom | Fix |
|---|---|---|
| row_num or column_num points outside the array | #REF! error | Keep the indexes within the range, or clamp them with MIN and MAX |
| row_num or column_num is non-numeric text | #VALUE! error | Make the index numeric; convert it with VALUE or compute it with MATCH |
| The range operator is applied to an INDEX result that is a value | #VALUE! error | Use the reference form so INDEX returns a reference, as in A1:INDEX(...) |
| area_num is larger than the number of areas | #REF! error | Use an area number from 1 to the count of areas in the reference |
| Non-adjacent areas are on different sheets | #VALUE! error | Keep all areas on one sheet, or use CHOOSE to pick the range |
| MATCH inside INDEX does not find the lookup value | #N/A error | Check the lookup value and use exact match with 0 as the third argument |
| Spilled array runs into occupied cells | #SPILL! error | Clear the spill range, or wrap the result in a function such as SUM |
Most INDEX problems are boundary or type problems. An index of 0 is
valid and means "the whole row or column", but any index larger than the array
dimensions returns #REF!. When the index is calculated, guard it
with MIN and MAX, for example
=INDEX(A1:A10, MAX(1, MIN(B1, 10))), so it can never fall outside
the range.
The INDEX function is one of Excel's most versatile tools for data
retrieval. Its array form returns values and arrays from any position, and its
reference form returns live references that make dynamic ranges possible. Setting
a position to 0 selects a whole row or column, pairing with MATCH
builds flexible lookups that beat VLOOKUP, and nesting inside
SUM or CELL turns references into powerful formulas.
Mastering both forms of INDEX, and remembering that indexes start at
1 while an out-of-range index returns #REF!, will significantly
enhance your Excel skills.
Author
List all Excel Formulas.