INDEX

What it does

INDEX(array, row_num, [column_num], [area_num])

INDEX retrieves what sits at given coordinates. Stated that plainly it sounds like array subscripting, and for the simple call it is: INDEX(a, 2, 3) is the element in the second row and third column of a.

Two things lift it above subscripting, and between them they account for nearly everything INDEX is used for:

1. A zero coordinate means "the whole axis". INDEX(a, 0, 3) is the entire third column; INDEX(a, 2, 0) is the entire second row. Zero is not an out-of-range index, it is a quantifier.
2. Given a reference, INDEX returns a reference — not the reference's value. That is why INDEX can appear as an endpoint of the range operator, be handed to functions that require a range, and serve as a non-volatile substitute for OFFSET.

Arguments

Positions that are commonly misunderstood:

• Coordinates are relative to the array or area, not to the worksheet. INDEX(C5:E9, 1, 1) is C5, not A1.
• Zero versus omitted. These are different: zero selects a whole axis; omitted (Missing) invokes the one-dimensional convenience or the documented default. The value model keeps Missing distinct from every other "nothing" (value universe), and INDEX is one of the functions where the distinction is load-bearing rather than pedantic.
• area_num exists only in the reference form, and a multi-area reference passed inline needs the double-parenthesis grammar described on the AREAS page, because the comma would otherwise separate arguments.

Try it

Quirks

The two forms

Microsoft's documentation presents INDEX as two functions sharing a name.

Array form — INDEX(array, row_num, [column_num])

Operates on an array value and returns a value (or, with a zero coordinate, an array). This is the form you get when the first argument is an array constant, an array-valued expression, or a range in a context where the result is consumed as a value.

Microsoft documents a convenience for one-dimensional inputs: if array has only one row or only one column, the corresponding index argument is optional. So for a column a, INDEX(a, 5) means the fifth element — the missing column_num is not defaulted to 1 by accident, it is defined away because there is only one column.

Reference form — INDEX(reference, row_num, [column_num], [area_num])

Operates on a reference and returns a reference. It gains a fourth argument, area_num, which selects which rectangle of a multi-area reference to index into; Microsoft documents the default as area 1. This is the form that composes with the reference algebra.

The two forms are not selected by a mode flag. Which one applies follows from what the first argument is and from what the calling context does with the result — which is exactly why INDEX feels different in different formulas, and why the reference-returning behaviour surprises people who have only used the array form.

The practical consequences of the reference form are worth listing explicitly, because they are the reason experienced modellers reach for INDEX:

• As a range endpoint. SUM(A1:INDEX(A:A, n)) sums from A1 to a computed last row. The range operator needs references on both sides; because INDEX supplies one, this works, and it recomputes only when its inputs change.
• As a non-volatile OFFSET. OFFSET is volatile: it recalculates on every recalculation of the workbook. INDEX is recorded volatility_class: NonVolatile. For dynamic-range definitions this is the difference between a workbook that recalculates lazily and one that does not.
• As a target for functions that require a range, such as those that need a reference rather than its contents.

INDEX is sometimes described as usable "on the left of an assignment", which is a loose way of saying that it designates a location rather than a value. The worksheet has no assignment, so the accurate statement is the one above: it returns a reference, and references are what location-consuming constructs need.

Result and edge cases

Return kind: a scalar, an Array, or a Reference, depending on form and on whether a coordinate is zero.

• Both coordinates zero, reference form — the whole reference (or the whole selected area).
• One coordinate zero — a whole row or column. In the array form this is an array that spills in modern Excel and required array entry historically; in the reference form it is a reference to that row or column, which is the version that composes with SUM and the range operator.
• Single-cell result in the reference form. It is still a reference; the surrounding expression decides whether it is dereferenced. A bare INDEX in a cell displays the value.
• Blank located cell. Empty is admitted at the raw-return boundary but not at the published one, so an INDEX onto an empty cell publishes as numeric zero rather than as blank — the general normalization described in the value universe, not something INDEX decides.
• Error in the located cell is simply the result.
• Array-valued coordinates. The reference implementation's kernel carries both a scalar selector and an array selector, so a vector of row numbers is a shape its structure anticipates. What Excel returns for INDEX(a, {1;3}) — a spilled vector, an implicit intersection, or an error — is not established here, and it differs between the pre- and post-dynamic-array eras.
• Implicit intersection. In pre-dynamic-array Excel, an INDEX result that was an array or multi-cell reference in a scalar context underwent implicit intersection; modern Excel spills instead unless @ is written. Formulas migrated from older workbooks carry the @ marker for exactly this reason, and the difference is a compatibility question rather than a semantic one.

Errors

Microsoft's page documents:

• #REF! when row_num, column_num or area_num is out of range for the given array or reference — including an area_num larger than the number of areas present.
• #VALUE! for arguments that are not usable as indices.

The split follows the same logic as HLOOKUP's: a coordinate that points outside the data is a reference failure; a coordinate that is not a usable number is a value failure. The reference implementation's error enumeration mirrors this shape, carrying distinct out-of-bounds and invalid-index cases.

An error value supplied as an argument propagates under the universal rule in coercion and lifting.

How to implement

Notes for implementers

• The reference must not be dereferenced early. This is the whole function. An implementation that resolves reference to values on entry can still return correct scalars and will fail every composition — range endpoints, SUM over a computed column, dynamic named ranges. The correctness of INDEX is not visible from its scalar results.
• Zero is a distinct branch, in each coordinate independently. Four combinations (both nonzero, row zero, column zero, both zero), and the both-zero case is meaningful only in the reference form.
• Missing is a third state alongside zero and a positive index. The one-dimensional convenience means an implementation must know whether the argument was omitted, not merely what its value was — which is exactly the Missing-versus-Empty distinction the shared model insists on carrying to the call boundary.
• Index coercion has a documented shape in the reference implementation: the index is coerced to a number and rejected unless it is finite, non-negative and integral. Non-negative rather than positive, because zero is legal — an implementation that validates > 0 breaks the whole-axis form.
• area_num selection precedes coordinate bounds checking, since the bounds depend on which area was selected. Doing it in the other order range-checks against the wrong rectangle.
• The array-selector path in the reference implementation (a selector that may be a scalar or an array) is the seam where dynamic-array behaviour enters. It is also the least established part of this page.

Rust code

eager(
    INDEX,
    "INDEX",
    CallableArityShape::range(2, 4),
    &[
        Parameter::value_or_reference("array"),
        Parameter::value("row_num"),
        Parameter::value("column_num"),
        Parameter::value("area_num"),
    ],
    index,
)

No implementation has been published with a Handbook test suite for this function.

Version differences

No comparison between specific Excel builds is recorded here yet. This does not establish that all builds behave alike.

Alternatives

Relationships

• MATCH is the other half of the INDEX/MATCH idiom: MATCH computes a position, INDEX retrieves it. The pair replaces VLOOKUP/HLOOKUP and removes their two structural limits — the key column need not be first, and the result index need not be counted by hand.
• XLOOKUP subsumes the common INDEX/MATCH case in one call. INDEX/MATCH remains the more flexible construction, particularly for two-dimensional lookups (INDEX(a, MATCH(...), MATCH(...))) and for anything needing a reference result.
• OFFSET also computes a reference and is volatile; INDEX is not. Where an OFFSET formula can be rewritten with INDEX, it usually should be.
• AREAS counts the areas that area_num selects among; INDEX(ref, r, c, AREAS(ref)) is the "last area" idiom.
• CHOOSEROWS / CHOOSECOLS are the modern, array-returning way to do the zero-coordinate trick for several rows or columns at once — but they return values, not references, so they do not substitute for the reference form.
• INDIRECT is the other route to a computed reference, via text. It is volatile and invisible to the dependency graph; INDEX is neither. When both would work, INDEX is the better-behaved choice.
• The range operator (FUNC.OP_RANGE_REF, :) is INDEX's most important collaborator in the reference form.

Named variants

No named variants are recorded yet.