INDEX: get the value at a known position

INDEX is easiest to understand as picking a cell from a range by row and column position. That sounds modest, but paired with MATCH it becomes a reliable way to build flexible lookups.

Formula syntax

=INDEX(array, row_num, [column_num])

What goes into it

A quick example first

=INDEX(C2:C100,5)

=INDEX(B2:D20,MATCH(H2,A2:A20,0),2)

Return a value by its position inside a range

Enter this three-by-two data block in B2:C4. These are cell positions, not a requirement to add a header within that range.

Row 2 of B2:C4 is worksheet row 3. This difference explains many off-by-one mistakes when a range begins below the top of a sheet. MATCH returns the position 3 for Folder, so INDEX returns the third value of C2:C4.

Before you copy this into a real sheet

Test MATCH on its own when a combined lookup fails. Lock source ranges before filling the formula. Align the first row of the lookup array with the first row of the return array: equally sized but shifted ranges can still return a wrong answer. The array form shown here is sufficient for simple lookups; do not introduce INDEX’s more complex reference form unless you actually need to choose among separate areas.

Useful habits

• INDEX returns from the range you give it; row and column numbers are relative to that range.
• Pair INDEX with MATCH/XMATCH when the lookup column is not fixed.
• Test MATCH separately before combining functions during debugging.

Where people get caught out

• Using worksheet row numbers when the INDEX array starts below row 1.
• Supplying a row or column position outside the array.

Other functions worth a look

A detail worth remembering

The row number is counted inside the range you pass to INDEX, not from the top of the worksheet. That distinction matters when your range starts on row 5.