What INDEX does
INDEX returns a value from a range once you know its position. Instead of searching directly for a key the way VLOOKUP does, INDEX waits for a row and column coordinate and then returns the value at that intersection.
That makes it a flexible building block in lookup formulas, especially when combined with MATCH.
Practical examples
Return a value from a two-column range
=INDEX($A$2:$B$13,5,2)
This returns the value from row 5, column 2 of the selected range.
Pair INDEX with MATCH
=INDEX($B$2:$B$13,MATCH(E2,$A$2:$A$13,0))
This pattern is popular because MATCH finds the position and INDEX returns the value.
Common mistakes and notes
Mixing up worksheet positions with array positions
INDEX counts from the top-left cell of the selected array. Row 1 means the first row of the range, not necessarily worksheet row 1.
Forgetting that one-dimensional arrays need fewer arguments
If your array is a single column, you often need only row_num. If it is a single row, you may only need column_num.
Using INDEX when a direct cell reference is simpler
INDEX is powerful, but not every sheet needs it. Use it when the position needs to be dynamic, not when the target cell is fixed.