Arguments
array- The range of cells to index into.row_num- The row position within the array.column_num(optional) - The column position within the array (for 2-D ranges).
INDEX
Returns the value at a given row (and optional column) position within a range.
Syntax
=INDEX(array, row_num, [column_num])=INDEX(B2:B100, 5)Returns: The 5th value in B2:B100
One-dimensional lookup by position.
=INDEX(B2:B100, MATCH(A2, C2:C100, 0))Returns: The B value on the row where C equals A2
Paired with MATCH, this is the flexible left-or-right lookup that predates XLOOKUP.
How INDEX evaluates
INDEX answers a positional question: given a range and a row number, hand back what is in that position. It does no searching at all. That sounds limiting until you notice it is the half of a lookup that VLOOKUP hard-codes - VLOOKUP finds a row and reads a column in one step, while INDEX reads the column and lets you supply the row from anywhere, usually from MATCH.
Positions are relative to the range you pass, not to the sheet. In =INDEX(B5:B40, 3) the 3 means the third cell of that range, which is B7. Row and column numbers start at 1, and a number larger than the range gives #REF! rather than reading past the edge. Passing 0 is not an error: row 0 means every row, so INDEX returns a whole column, and column 0 returns a whole row.
There are two forms. The array form, described here, returns values. The lesser-known reference form takes several areas as the first argument plus a final area_num and returns a reference rather than a value, which is what lets =SUM(INDEX(A1:A10, 1):INDEX(A1:A10, 5)) build a range out of two computed endpoints. Both are available everywhere INDEX is, which is every spreadsheet application ever shipped.
- Row 0 or column 0 returns the whole vector
- =INDEX(A2:C20, 0, 2) returns all nineteen values of the second column as an array, and =INDEX(A2:C20, 5, 0) returns the whole fifth row. In Excel 2021 and 365 these spill; in older versions they must be array-entered or consumed by a function such as SUM.
- Omitting column_num on a 2-D range
- With a multi-column array and only a row number, INDEX returns that entire row as an array. On a single-column or single-row range the one index is taken as the position along it, which is why =INDEX(B2:B100, 5) needs no second argument.
- Fractional positions are truncated
- A row number of 3.9 is treated as 3, not rounded to 4. Numbers held as text are coerced, so "3" works, but a value that cannot be read as a number gives #VALUE!.
- No sorting, no matching, no type rules
- INDEX never compares values, so none of the usual lookup traps apply: no case sensitivity, no text-versus-number mismatch, no sort requirement. Every one of those problems lives in whatever supplies the position.
- Returns the cell's value, including blanks as 0
- An empty cell at the requested position returns 0 in the array form, the same as VLOOKUP. Test with =IF(INDEX(...)="", "", INDEX(...)) when a report must show a gap rather than a zero.
Errors INDEX returns
| Error | What it means and how to fix it |
|---|---|
| #REF! | row_num or column_num is larger than the range has rows or columns - the commonest cause is a MATCH computed over a range of a different height from the one INDEX reads. Line up the two ranges so they start and end on the same rows. |
| #VALUE! | A position argument is negative, or is text that cannot be read as a number. It also appears in the reference form when area_num points at an area that does not exist. |
| #N/A | INDEX itself rarely returns this; when you see it, the #N/A is coming from the MATCH inside it and simply passing through. Evaluate the MATCH on its own to confirm before changing the INDEX. |
| #SPILL! | A row 0 or column 0 call returned a whole vector into a sheet where the cells below or to the right are occupied. Clear them, or wrap the call in an aggregate that reduces the array to one value. |
| #NAME? | The function name is misspelled, or a named range used as the array no longer exists in the workbook. |
More examples
=INDEX($B$2:$E$200, MATCH($A2, $A$2:$A$200, 0), MATCH(B$1, $B$1:$E$1, 0))Returns: The cell where a row key and a column header cross
Two MATCH calls make a two-way lookup out of one INDEX. The mixed $ anchors let the same formula be dragged across a whole grid.
=SUM(INDEX($C$2:$F$100, 0, MATCH($A2, $C$1:$F$1, 0)))Returns: The total of whichever column the header in A2 names
Row 0 hands SUM the entire column as an array, so the column being totalled is chosen by name rather than by editing the formula.
=INDEX(A2:A100, COUNTA(A2:A100))Returns: The last non-empty value in the column
COUNTA gives the number of filled cells, which is the position of the last one as long as the column has no gaps.
Compatibility
| Application | Support |
|---|---|
| Excel 2007-2016 | Fully supported, both the array and reference forms. Array results need Ctrl+Shift+Enter. |
| Excel 2019 | Identical. INDEX/MATCH is the standard lookup here because XLOOKUP is absent. |
| Excel 2021 / Microsoft 365 | Unchanged, but row 0 / column 0 results now spill automatically instead of needing an array entry. |
| Google Sheets | Same syntax and same 0-means-whole-vector rule. Sheets has no reference form for building ranges from two INDEX calls. |
| LibreOffice Calc | Supported with the same arguments; array results are entered with Ctrl+Shift+Enter as in older Excel. |
Does INDEX Work in Google Sheets?
Yes. INDEX uses the same syntax in Google Sheets and LibreOffice Calc, so you can paste these formulas straight across.
In the ExcelTool editor
The ExcelTool editor implements INDEX with two or three arguments and follows Excel's positional rules: indices are 1-based, fractional values are truncated, and a negative row or column returns #VALUE!.
Row 0 returns the whole requested column and column 0 the whole row, each as an array that spills into the sheet, and a two-argument call against a single-row or single-column range takes that one index along the range. A position past the end of the range returns #REF!.
Only the array form is implemented. The reference form that takes several areas plus an area_num, and the =INDEX(...):INDEX(...) idiom for building a range from two computed endpoints, are not supported in the editor.
You can try any of these formulas in the free in-browser spreadsheet editor - it opens .xlsx files, recalculates as you type, and needs no signup.
Build a INDEX formula
Fill in your ranges and copy a ready-to-paste formula - no signup.
INDEX questions
Three concrete reasons. The return column can be left of the key column, which VLOOKUP forbids. Inserting or deleting a column inside the table does not break the formula, because there is no col_index_num to go stale. And when you need several columns from the same matched row, MATCH can be computed once in a helper cell and reused by every INDEX, so the table is scanned once instead of once per column.
The entire column named by column_num, as an array rather than a single value. It is not an error and not a blank. In Excel 2021 and 365 the array spills down the sheet; in Excel 2019 and earlier it only works inside a function that expects an array, or when the formula is committed with Ctrl+Shift+Enter. The same rule holds with the arguments swapped: column 0 returns the whole row.
Yes, through the reference form. =SUM(INDEX(A:A, 5):INDEX(A:A, 20)) builds the range A5:A20 from two computed endpoints, because in that position INDEX yields a reference rather than a value. It is how dynamic running totals are written without OFFSET, and unlike OFFSET it is not volatile, so it does not force a recalculation on every edit.
It has to be the same height, and it has to start on the same row. INDEX counts positions inside its own range, so if MATCH searches A2:A100 and INDEX reads C1:C99, every answer is off by one row - a mistake that produces plausible wrong values rather than an error. Anchoring both ranges with $ and building them from the same start row is the reliable habit.