INDEX Function

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.

INDEX is one of Excel's most important lookup tools because it returns a value from a position you define. That makes it flexible enough to work with exact positions, dynamic positions found by MATCH, and even range-building patterns where the endpoint itself should move.

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.

This is a strong beginner example because it shows INDEX at its most direct. Once you know the position, the function simply returns whatever is sitting there.

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

This is where INDEX becomes much more useful in practice. MATCH finds the location, and INDEX turns that location into the real value you actually want back.

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

That makes the example helpful for modern Excel users working with spill behavior. INDEX is not limited to a single cell result when the formula setup allows a larger return.

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

This shows another important side of INDEX: it can help build ranges, not just pull values. That makes it very useful in totals, charts, and formulas that depend on changing endpoints.

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

Conclusion Recap

INDEX is one of the most useful lookup functions because it returns the value at a position you point to. In this lesson, that included pulling one item from a table, pairing with MATCH, returning full rows or columns, and using INDEX as the end of a dynamic range.

The easiest way to think about it is this: another formula can figure out the position, and INDEX can return what is sitting there. Once that clicks, INDEX becomes much easier to read and trust.

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