Summary

The INDEX function returns a value from a range based on its row number and, if needed, its column number. If you know the position inside the range, INDEX returns the value at that position.

INDEX is useful because it separates position from result. One formula can figure out where something is, and INDEX can return the value from that location. This is why INDEX is often paired with MATCH in flexible lookup formulas.

Syntax

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

The basic idea is simple: give INDEX a range, then tell it which row and column to use inside that range. For example, =INDEX(A1:C3,2,3) returns the value from the 2nd row and 3rd column of that block.

In everyday worksheets, the most common form is the array form with array, row_num, and sometimes column_num. The area_num argument is only needed when INDEX is working with more than one separate area.

Arguments

• array - The range or array you want to return a value from.
• row_num - The row position inside that range.
• [column_num] - Optional. The column position inside that range.
• [area_num] - Optional. Used when the reference contains multiple separate areas.

If array is a single column, you usually only need row_num. If it is a two-dimensional range, you usually need both row and column. In current Excel versions, using 0 for one of the position arguments can return a full row or column from the array.

INDEX vs Other Functions

INDEX is mainly about returning a value from a position, while other lookup functions may search and return in one step.

Using INDEX

The most common use of INDEX is value retrieval by position. If a formula already knows the row and column numbers, INDEX can return the correct item from the range without searching the whole table again.

INDEX becomes much more useful when paired with MATCH. MATCH finds the position of a lookup value, and INDEX uses that position to return the result from another column or row. This is one reason INDEX is often used in place of older VLOOKUP patterns.

INDEX is also useful for dynamic ranges. A formula such as A1:INDEX(A:A,10) uses INDEX as the endpoint of a range. That makes it useful in sums, charts, and formulas where the range boundary should come from a calculation instead of being typed by hand.

• Use INDEX when the worksheet needs a value from a known row and column position.
• Use INDEX with MATCH when one formula should find the position and the other should return the value.
• Use INDEX inside a range reference when the endpoint should be calculated dynamically.

Example 1 - Return a Value from a Matrix

This formula uses a row number and a column number to return one value from a 2D block. If the requested position is row 2, column 3, INDEX returns the value sitting at that exact intersection.

=INDEX(A1:C3, 2, 3)

Example 2 - Pair INDEX with MATCH

In this pattern, MATCH finds the position of 102 in the ID list, and INDEX uses that position to return the matching part name from the name list. This is one of the most common reasons to use INDEX in real workbooks.

=INDEX(A2:A10, MATCH("B101", B2:B10, 0))

Example 3 - Return a Full Row or Column

When one of the position arguments is 0, newer Excel versions can return a whole row or column from the array. This is useful when a formula needs an entire slice of a table instead of just one cell.

=INDEX(A1:M100, 5, 0)

Example 4 - Use INDEX as the End of a Range

Here INDEX does not just return one visible value. It acts as the end point of the range inside the SUM formula. This is a useful pattern when the worksheet needs a dynamic range boundary instead of a fixed typed reference.

=SUM(A1:INDEX(A:A, 10))

Conclusion Recap

• Main job: INDEX returns a value from a position inside a range.
• Common pattern: It is often paired with MATCH for flexible lookups.
• Useful extra: In newer Excel, a 0 argument can return a full row or column.
• Range trick: INDEX can also be used as the endpoint of a dynamic range.