Excel function

INDEX in Excel: return a value by position

INDEX returns the value at a specific position inside a range. It does not search by itself, but it becomes powerful when paired with MATCH.

What INDEX does

INDEX returns a value from a range based on a row number and, when needed, a column number. Its common syntax is `=INDEX(array, row_num, [column_num])`.

If the range is one column tall, you usually only need the row number. For example, `=INDEX(B2:B10,3)` returns the third value from B2:B10. If the range has multiple columns, the optional column number tells Excel which column inside the selected array to return.

INDEX is not a search function by itself. It answers "give me the value at this position." MATCH can answer "where is this item?" Together, `INDEX` and `MATCH` can build a flexible lookup.

Arguments explained

`array` is the range that contains the answer values, such as B2:D10. `row_num` is the position within that range, not necessarily the worksheet row number. If your array starts at row 2, row_num 1 means the first row of the array.

`column_num` is optional for one-column arrays. For a multi-column array like B2:D10, column_num 1 means column B inside the array, 2 means column C, and 3 means column D.

This position-based behavior makes INDEX precise. It also means INDEX needs a correct position. If you want to find a product code, employee ID, or student name, use MATCH to calculate the row number first.

Practical examples

A beginner example is returning a value from a list. If B2:B8 contains student names, `=INDEX(B2:B8,4)` returns the fourth name in that range.

A business example is returning monthly sales from a matrix. If B2:D6 contains Q1 sales for several reps, `=INDEX(B2:D6,3,2)` returns the value from the third row and second column of that selected block.

With MATCH, the formula becomes searchable: `=INDEX(C2:C20,MATCH(A2,B2:B20,0))`. Here A2 is the product code to find, B2:B20 is the product-code list, and C2:C20 contains prices. MATCH finds the row position, and INDEX returns the price from that position.

When to use it

Use INDEX when you know a row and column position, when you want a flexible lookup with MATCH, or when you need a formula that separates "find the position" from "return the value."

Do not use INDEX alone when your real task is searching for a value. INDEX cannot magically find a product or name without a position. In modern Excel, XLOOKUP is often easier for straightforward lookups, but INDEX and MATCH remain valuable for understanding lookup logic and older files.

Practical examples

Return the fourth list item

=INDEX(B2:B8,4)

Returns the fourth value inside B2:B8.

INDEX with MATCH lookup

=INDEX(C2:C20,MATCH(A2,B2:B20,0))

MATCH finds the position of A2 in B2:B20, and INDEX returns the value from C2:C20 at that same position.

Recommended Excel functions

INDEXMATCHXLOOKUPVLOOKUPCHOOSE

Common mistakes

  • 01Expecting INDEX alone to search for a value.
  • 02Confusing worksheet row numbers with positions inside the selected array.
  • 03Forgetting the column number when using a multi-column array.
  • 04Using mismatched ranges when combining INDEX with MATCH.

Practice

Practice this skill with interactive exercises on Calcu.

Use Calcu to practice INDEX in realistic spreadsheet exercises with instant feedback.

Open Calcu

FAQ

Does INDEX search for a value?

No. INDEX returns a value by position. Use MATCH or another function to find the position first.

What does row_num mean in INDEX?

row_num is the position inside the selected array, not necessarily the row number on the worksheet.

Why use INDEX with MATCH?

MATCH finds the position of a lookup value, and INDEX returns the related value from another range.