Excel INDEX Function

INDEX returns the value at a specific row and column position within a range or array. It doesn't search for anything on its own — that's MATCH's job. Pair the two and you get a lookup formula that can go left, go vertical and horizontal at once, and survive someone inserting a column into your source table.

Available in: Excel 365, Excel 2021, Excel 2019, Excel 2016, and earlier. INDEX itself works the same way in every version. What changes across versions is how it handles spilled results: setting row_num or column_num to 0 spills automatically in Excel 365, but in Excel 2019 and earlier it needs Ctrl+Shift+Enter or a wrapping function to display more than one value.

Syntax

INDEX has two forms. Most people only ever use the array form.

=INDEX(array, row_num, [column_num])

=INDEX(reference, row_num, [column_num], [area_num])

Parameter	Required	Description
array	Yes	The range or array you're pulling a value from. Can be a single column, a single row, or a 2D block.
reference	Yes	Same idea as array, but can be one or more separate, non-contiguous ranges wrapped in parentheses — that's what unlocks area_num.
row_num	Yes	Which row inside array to pull from, counted from the top of the range — row 1 of the range, not row 1 of the sheet. 0 returns the entire column.
column_num	No	Which column inside array to pull from. Omit it for a single-column array. 0 returns the entire row.
area_num	No	Only used with reference form. Picks which of the ranges inside reference to pull from, when you've supplied more than one. Defaults to 1.

Basic Example

You're tracking Q3 commissions in a named range called commissions — Employee ID, Name, Region, and Q3 Sales, 50 rows deep.

=INDEX(commissions, 14, 4)

// commissions — the full data range
// 14          — the 14th row of the range
// 4           — the 4th column (Q3 Sales)

INDEX doesn't search for row 14. It just goes there. That's the entire function — no lookup logic, no matching, just coordinates in, value out. The row and column numbers rarely come from a hardcoded number like this in practice. Usually they come from MATCH, which is where INDEX gets its reputation.

How INDEX Works

Array form returns a value, reference form returns a reference

The array form — INDEX(array, row_num, [column_num]) — is what almost every formula uses. It returns a value. The reference form adds a fourth argument, area_num, and lets reference hold multiple separate ranges wrapped in an extra set of parentheses. Reach for reference form only when you genuinely need to pull from more than one disconnected block of cells in a single formula.

row_num or column_num set to 0 returns an entire row or column

Set row_num to 0 and INDEX returns the whole column. Set column_num to 0 and it returns the whole row, as an array.

=INDEX(commissions, 0, 3)   // returns every Region value in the range

In Excel 365, that array spills automatically into the cells below your formula. On Excel 2019 and earlier, the same formula only shows the first value unless it's entered as a legacy array formula with Ctrl+Shift+Enter, or nested inside a function that expects an array — SUM, COUNTIF, or another INDEX/MATCH.

INDEX and MATCH replace hardcoded column numbers

MATCH finds a position. INDEX uses that position as a coordinate. Together they eliminate the column-counting problem that makes VLOOKUP fragile.

=INDEX(commissions, MATCH(H4, employees, 0), 4)

// H4        — the employee name you're looking up
// employees — the Name column, searched by MATCH
// 4         — still hardcoded here, but doesn't have to be

Nest a second MATCH in place of the 4 and the column position becomes dynamic too. Insert a column into commissions and the formula still points at the right data, because it's matching a header, not counting positions.

area_num pulls from one of several non-contiguous ranges

Reference form accepts more than one range and lets you pick which one to read from at runtime.

=INDEX((north_sales, south_sales), 3, 2, 2)

// (north_sales, south_sales) — two separate ranges, same shape
// 3                          — row 3 within whichever range is selected
// 2                          — column 2
// 2                          — area_num: use the second range, south_sales

This is niche. Most people never touch area_num. But it's the cleanest way to switch between two regional tables without duplicating the formula or building a helper column to flag which table to use.

Common Use Cases

Two-way lookup — row and column both dynamic

Pull a value from the intersection of a row you name and a column you name, without knowing either position in advance.

=INDEX(sales_grid, MATCH(H4, employees, 0), MATCH(H5, quarter_headers, 0))

// MATCH(H4, employees, 0)       — finds which row the employee is in
// MATCH(H5, quarter_headers, 0) — finds which column the quarter is in

Add a new quarter column or reorder employees alphabetically and the formula keeps working. VLOOKUP would need its column number rewritten by hand.

Left lookup

Return a value from a column to the left of the column you're matching on — something VLOOKUP structurally cannot do.

=INDEX(employee_id_col, MATCH(H4, employee_name_col, 0))

Here H4 holds a name, and the ID column sits to the left of the name column in the source table. VLOOKUP requires the lookup value in the leftmost column. INDEX doesn't care which side anything is on — it just returns whatever's at the matched position.

Multi-criteria lookup with boolean array multiplication

Match on two or more conditions at once by multiplying boolean arrays inside MATCH.

=INDEX(returns, MATCH(1, (H2=regions)*(H3=quarters), 0))

// (H2=regions)*(H3=quarters) — TRUE*TRUE = 1 only where both conditions match
// MATCH(1, ..., 0)           — finds the row where that product equals 1

Every row where region doesn't match H2 or quarter doesn't match H3 multiplies out to 0. Only the row satisfying both conditions produces a 1. In Excel 365, this runs as a normal formula, no Ctrl+Shift+Enter required — Excel's native dynamic array engine handles the multiplication automatically.

Building a dynamic named range without OFFSET's volatility

If you're maintaining a named range that needs to expand as new rows get added, INDEX is the better foundation than OFFSET.

// Named range definition: ProductList
=Sheet1!$A$2:INDEX(Sheet1!$A:$A, COUNTA(Sheet1!$A:$A))

OFFSET recalculates on every single change to the workbook, because it's a volatile function. INDEX only recalculates when something in its own dependency chain changes. On a large workbook with dozens of dynamic ranges, that difference is the gap between a spreadsheet that opens instantly and one that stutters on every keystroke.

Handling Errors

INDEX throws two catchable errors, and both mean something specific.

Common causes of #REF!:

• row_num or column_num points outside the actual dimensions of array — row 60 in a 50-row range, for example
• area_num refers to a range that doesn't exist in the reference list
• The array itself was deleted or the reference now points at nothing

Common causes of #VALUE!:

• row_num or column_num is text instead of a number
• The array argument spans more than one worksheet without being wrapped as a proper multi-area reference

=IFERROR(INDEX(commissions, MATCH(H4, employees, 0), 4), "Not found")
=IFNA(INDEX(commissions, MATCH(H4, employees, 0), 4), "")   // use IFNA if you only want to catch a failed MATCH

Notes & Gotchas

What does the INDEX function do in Excel?

INDEX returns a value or a cell reference from a specific position within a range or array. It takes a row number, an optional column number, and hands back whatever sits at that intersection. On its own it doesn't search for anything — pairing it with MATCH is what turns it into a lookup tool.

What is the difference between INDEX and VLOOKUP?

VLOOKUP can only look right — the lookup value has to sit in the leftmost column of the table, and the return column must be to its right. INDEX/MATCH has no such restriction; the columns can be in any order, and the return column can sit anywhere relative to the match column. INDEX/MATCH also survives column insertions, because it references columns by name through MATCH instead of counting position numbers.

How do you use INDEX and MATCH together in Excel?

MATCH finds the position of a value within a range and returns that position as a number. INDEX takes that number as its row_num or column_num argument and returns the corresponding value from the target range. The combination replaces the hardcoded column numbers that make plain VLOOKUP formulas break when a table's structure changes.

What are the arguments of the INDEX function?

Array form takes array (required), row_num (required), and column_num (optional). Reference form swaps array for reference and adds a fourth argument, area_num, which selects among multiple ranges supplied in reference. Almost every real-world formula uses array form.

Can INDEX return an entire row or column?

Yes. Setting row_num to 0 returns the entire column at column_num. Setting column_num to 0 returns the entire row at row_num. In Excel 365, that result spills into adjacent cells automatically; in older versions it needs to be entered as a legacy array formula or nested inside another function to display more than the first value.

Why does INDEX return #VALUE! when the range spans multiple sheets?

All the cells inside array or reference must live on one worksheet. Reference something like Sheet1:Sheet3!A1:A10 and INDEX throws #VALUE! — it can't resolve a range that crosses sheets, so it fails outright rather than returning a result of any kind. If you genuinely need to pull from multiple sheets in one formula, use reference form with area_num, supplying each sheet's range as a separate area.

Why does INDEX return #REF!?

row_num or column_num points past the actual size of the array — asking for row 60 in a range that only has 50 rows, for instance. This also happens with area_num in reference form if it points to a range that isn't one of the ones actually supplied. Double-check that any MATCH feeding into INDEX is searching the same range INDEX is indexing into; a mismatched range size is the usual culprit.

Related Functions

Function	Use this when...
VLOOKUP	Your lookup value is already in the leftmost column and you don't need left lookups or multi-criteria matching.
XLOOKUP	You're on Excel 365 or 2021 and want built-in left-lookup and not-found handling without pairing two functions.