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.
Returns the value at a row and column of an array. The reverse of MATCH: INDEX fetches by position, MATCH finds the position.
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.
| Expression | Result | Why |
|---|---|---|
| =INDEX(A2:A6,3) | Carol | 1D: the 3rd item of a single column |
| =INDEX(A2:D6,3,3) | 75000 | 2D: row 3, column 3 (Carol's salary) |
| =INDEX(A2:D6,1,1) | Alice | 2D: the top-left cell of the array |
| =INDEX(A2:D6,4,2) | Eng | 2D: row 4, column 2 (Dan's dept) |
| =INDEX(A2:D6,3,4) | 90 | 2D: row 3, column 4 (Carol's score) |
| =INDEX(C2:C6,MATCH("Carol",A2:A6,0)) | 75000 | INDEX/MATCH: find Carol, fetch her salary |
| =INDEX(A2:A6,MATCH(75000,C2:C6,0)) | Carol | Left-lookup: find a salary, return the name |
| Expression | Result | Why |
|---|---|---|
| =INDEX(A2:A6,MATCH(75000,C2:C6,0)) | Carol | Key is in column C, result is in column A — to the LEFT |
| =INDEX(C2:C6,MATCH("Carol",A2:A6,0)) | 75000 | The same duo fetching to the right, like VLOOKUP |
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.
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.
INDEX is available across modern Excel, Google Sheets and LibreOffice. It has been in Excel since 2003.
| Platform | Support | Notes | Gotcha |
|---|
INDEX is one piece. The full course walks the toolkit end to end with live, measured examples.