Lookup · fetch by coordinate · Nov 2026

The INDEX function,
measured & explained.

Syntax, a live playground, a measured coordinate gallery (1D/2D), the INDEX/MATCH left-lookup, and a sourced error library: fetching a value by position.

D1
fx
=INDEX(A2:D6, 3, 3)
0
measured gallery rows
0
sourced errors fixed
0
platforms checked
// 01 — Anatomy

How INDEX works

Returns the value at a row and column of an array. The reverse of MATCH: INDEX fetches by position, MATCH finds the position.

=INDEX(array, row_num, [column_num])
arrayThe range to read from.
row_numWhich row (1-based) to return.
column_numOptional: which column; omit for a 1D range.
.bool-moat td.res-text{color:var(--cyan);font-weight:700;font-family:var(--mono)}
// 02 — Fetch by coordinate

What INDEX actually returns

INDEX returns the value at a given row and column — the reverse of MATCH. Paired with MATCH it becomes the lookup that beats VLOOKUP: it can look left, and it never breaks when columns move. Every row was measured live in HyperFormula on the sample table. Click any row to load it.

Coordinate gallery — 1D, 2D, and INDEX/MATCH

ExpressionResultWhy
=INDEX(A2:A6,3)Carol1D: the 3rd item of a single column
=INDEX(A2:D6,3,3)750002D: row 3, column 3 (Carol's salary)
=INDEX(A2:D6,1,1)Alice2D: the top-left cell of the array
=INDEX(A2:D6,4,2)Eng2D: row 4, column 2 (Dan's dept)
=INDEX(A2:D6,3,4)902D: row 3, column 4 (Carol's score)
=INDEX(C2:C6,MATCH("Carol",A2:A6,0))75000INDEX/MATCH: find Carol, fetch her salary
=INDEX(A2:A6,MATCH(75000,C2:C6,0))CarolLeft-lookup: find a salary, return the name

The left-lookup VLOOKUP cannot do

ExpressionResultWhy
=INDEX(A2:A6,MATCH(75000,C2:C6,0))CarolKey is in column C, result is in column A — to the LEFT
=INDEX(C2:C6,MATCH("Carol",A2:A6,0))75000The same duo fetching to the right, like VLOOKUP
⚠ INDEX needs a position, not a lookup value — use MATCH to turn a value into a position. A row or column number beyond the array gives #REF! in Excel. The INDEX array and the MATCH range must cover the same rows, or the result misaligns.
Why INDEX/MATCH beats VLOOKUP: it looks left, it is insert-safe (no hard-coded column index), and it is faster on large tables because it only reads two columns.
// 03 — Try it live

Interactive INDEX playground

Edit the table or type any formula — the engine evaluates instantly. Load a preset to see INDEX fetch by row and column, pair with MATCH for a full lookup, and reach a column to the left of the key.

A1Sample data — click any cell to edit, the formula re-evaluates live
Result
—
✓ readyHyperFormula 3.5
// 04 — Error library

The real-world error library

8 failure patterns mined from actual user reports on Reddit, Stack Overflow and Super User — each with the authentic symptom, root cause, a fix, and a runnable formula. Not textbook theory.

// 05 — Cross-platform

Where INDEX works

INDEX is available across modern Excel, Google Sheets and LibreOffice. It has been in Excel since 2003.

PlatformSupportNotesGotcha
✓ Supported full INDEX◐ Partial works with caveatsCompiled from public docs · verify against your build
// Go deeper

Master coordinate lookups

INDEX is one piece. The full course walks the toolkit end to end with live, measured examples.

  • INDEX 1D vs 2D form
  • INDEX + MATCH — the flexible lookup
  • The left-lookup and insert-safe advantages over VLOOKUP
  • Aligning the return array with the MATCH range
Free while in beta
Browse the function library

No account needed. Every page has a live playground.

// 07 — FAQ

Frequently asked