INDEX Function

Returns the value at a given row and column position in a range.

Category

Lookup & Reference

Available in

All versions

Returns

A value or reference

Taught hands-on in Level 4: Advanced Dynamic Arrays & Logic · INDEX & MATCH module

Overview

INDEX is like map coordinates. Give it a range, a row number and (optionally) a column number, and it returns whatever sits at that spot.

On its own it's simple. Paired with MATCH, which works out the position for you, it becomes one of the most flexible lookups in Excel — and it works in every version.

Syntax

=INDEX(array, row_num, [column_num], [area_num])

Arguments

ArgumentRequired?What it does
arrayYesThe range to pick from.
row_numYesWhich row of the range. Use 0 to return the whole column.
column_numOptionalWhich column of the range. Use 0 to return the whole row.
area_numOptionalWhen array is several ranges in brackets, which one to use.

Examples

Example 1 — Picking by position

Return the item in the 3rd row, 2nd column of the product table.

F2fx=INDEX(A2:D6, 3, 2)
ABCD
1IDProductCategoryPrice
2P-101KeyboardAccessories45
3P-102MonitorDisplays210
4P-103MouseAccessories25
5P-104LaptopComputers1,150
6P-105WebcamAccessories60
Row 3 of A2:D6 is spreadsheet row 4. Column 2 is B. Result: Mouse.
=INDEX(A2:D6, 3, 2) Explain
Positions count from the start of the range, not from row 1 of the sheet.

Example 2 — INDEX + MATCH: the classic lookup

Find the price for a product name typed in F2. MATCH finds the row; INDEX fetches the price.

G2fx=INDEX(D2:D6, MATCH(F2, B2:B6, 0))
ABCDEFG
1IDProductCategoryPriceProductPrice
2P-101KeyboardAccessories45Laptop1,150
3P-102MonitorDisplays210
4P-103MouseAccessories25
5P-104LaptopComputers1,150
6P-105WebcamAccessories60
=INDEX(D2:D6, MATCH(F2, B2:B6, 0)) Explain
  1. MATCH(F2, B2:B6, 0) finds Laptop in position 4.
  2. INDEX(D2:D6, 4) returns the 4th price: 1,150.
This can look left too: =INDEX(A2:A6, MATCH(F2, B2:B6, 0)) returns the ID.

Example 3 — Two-way lookup

A table of monthly sales by region. Find the value where a chosen region and month meet.

G3fx=INDEX(B2:D5, MATCH(G1, A2:A5, 0), MATCH(G2, B1:D1, 0))
ABCDEFG
1RegionJanFebMarRegionEast
2North120135150MonthFeb
3East95110130Sales110
4South140125160
5West8090105
=INDEX(B2:D5, MATCH(G1, A2:A5, 0), MATCH(G2, B1:D1, 0)) Explain

One MATCH finds the row (East = 2), the other finds the column (Feb = 2). INDEX returns the crossing point.

Example 4 — Returning a whole column

Use 0 as the row number to get every row in a column — handy inside SUM.

=SUM(INDEX(B2:D5, 0, MATCH("Mar", B1:D1, 0))) Explain

This totals the March column (545). Change "Mar" to "Jan" and the total follows.

Example 5 — Lookup with multiple criteria

Find the price of an item matching both a category and a product name. Multiply the tests to get a 1 only where both are true, then MATCH the 1.

=INDEX(D2:D6, MATCH(1, (C2:C6="Accessories") * (B2:B6="Webcam"), 0)) Explain

Returns 60. In older Excel, confirm with Ctrl+Shift+Enter.

Common mistakes

  • #REF! errors. row_num or column_num is outside the range — e.g. row 8 of a 5-row range.
  • Off-by-one results. The lookup range and the return range start on different rows. Make them line up (B2:B6 with D2:D6).
  • Using MATCH without the 0. Without match_type 0, MATCH does an approximate match. Always include it for lookups.

Notes

Kyle Stott

Author

Kyle Stott

Certified Microsoft Excel Expert

Kyle has worked professionally with Microsoft Excel for over a decade and has consulted on and taught Excel best practices to over 400 companies across 30+ countries.