INDEX Function
INDEX retrieves a value from a specific row and column in a range.
Syntax
Parameters
| Parameter | Description | Required |
|---|---|---|
| array | The range or array from which to retrieve the value. | Required |
| row_num | The row number from which to retrieve the value. | Required |
| column_num | Optional. The column number from which to retrieve the value. | Optional |
Basic Example
Get value from specific position
=INDEX(A1:C5, 3, 2)Returns the value from the 3rd row and 2nd column of the range.
Advanced Examples
Example 1: INDEX with MATCH for flexible lookup
Advanced lookupCreate a lookup that can search left
=INDEX(B:B, MATCH(A1, C:C, 0))How INDEX Works
INDEX finds the value at the intersection of the specified row and column in the range.
Important Notes & Limitations
Row and column numbers must be within the range.
For dynamic arrays, INDEX can return multiple values.
Common Errors & Fixes
#REF!Row/column number exceeds range sizeFix: Check your row and column numbers.
Download Practice File
INDEX
indexPractice INDEX with Real Data
Download a sample CSV file with pre-populated data and practice exercises for the INDEX function. Works in both Excel and Google Sheets.
File format: CSV (comma-separated values) - opens in Excel, Google Sheets, and all spreadsheet apps
Related Tutorials
Absolute vs Relative Reference
Absolute vs relative reference in Excel explained: what $ really locks, when to use mixed refs, the F4 shortcut, and dynamic alternatives in Google Sheets.
Excel REF! Error Fix
The complete Excel REF error fix: why #REF! appears, which edits break cell references, and how to repair the damage or prevent it from ever returning.
INDEX MATCH Formula
Master the INDEX MATCH formula in Excel and Google Sheets step by step: exact syntax, left lookups, two-way lookups, and how it compares to VLOOKUP and XLOOKUP.
Master INDEX/MATCH for Two-Way Lookups
Learn how to use INDEX and MATCH together to create powerful two-way lookups that work in any direction, unlike VLOOKUP.
INDEX MATCH with Multiple Criteria
INDEX MATCH with multiple criteria lets you match on two or more columns in Excel and Google Sheets. Learn the exact array formula plus a cleaner XLOOKUP alternative.
MATCH Function Exact Match
MATCH with match_type 0 is Excel's exact-match lookup. See how MATCH finds a precise position in a range and how to use it safely with INDEX and XMATCH.
VLOOKUP Approximate Match
VLOOKUP approximate match explained: how TRUE range_lookup works, why data must be sorted ascending, tax and grade table examples, plus XLOOKUP alternatives.
VLOOKUP Return Multiple Columns
VLOOKUP is built to return one column, but you often need several. Learn four reliable ways to make VLOOKUP return multiple columns in Excel and Google Sheets.
VLOOKUP with Multiple Criteria
VLOOKUP only matches one column, but real lookups often need two or more. Learn four reliable ways to do a VLOOKUP with multiple criteria in Excel and Google Sheets.