fx
FunctionPedia
Quick Answer

INDEX Function

INDEX retrieves a value from a specific row and column in a range.

✓ Excel✓ Google SheetsExcel All versions

Syntax

INDEX
(array, row_num, [column_num])

Parameters

ParameterDescriptionRequired
arrayThe range or array from which to retrieve the value.Required
row_numThe row number from which to retrieve the value.Required
column_numOptional. The column number from which to retrieve the value.Optional

Basic Example

Get value from specific position

=INDEX(A1:C5, 3, 2)
ResultValue at row 3, column 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 lookup

Create a lookup that can search left

=INDEX(B:B, MATCH(A1, C:C, 0))
Result: Lookup result
Combines INDEX and MATCH for powerful two-way lookups.

How INDEX Works

INDEX finds the value at the intersection of the specified row and column in the range.

1
Select range
Define the range containing your data.
2
Specify row
Choose the row number to retrieve from.
3
Specify column
Choose the column number (optional for single-column ranges).
4
Enter formula
Type =INDEX(range, row, column).

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 size

Fix: Check your row and column numbers.

Download Practice File

Practice 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.

Works in Google SheetsCompatible with ExcelIncludes exercises

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.

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.

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 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.

Frequently Asked Questions

Can INDEX return an entire row or column?
Yes, omit row_num or column_num to return an entire column or row.
What is INDEX/MATCH?
INDEX/MATCH is a powerful combination that can look up values in any direction, unlike VLOOKUP.