The INDEX function returns the value of a cell or a reference to cells within a range, based on specified row and column numbers.

Syntax

1. Array Form:

=INDEX(array, row_num, [column_num])

• array: The range of cells or an array constant.
• row_num: The row number in the array.
• column_num (optional): The column number in the array.

1. Reference Form:

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

• reference: A range of cell ranges (or multiple ranges).
• row_num: The row number in the reference.
• column_num (optional): The column number in the reference.
• area_num (optional): Specifies which range to use if the reference contains multiple ranges.

Usage Examples

1. Array Form:

• Formula: =INDEX(A1:C10, 2, 3)
• Returns the value in the 2nd row and 3rd column of the range A1:C10.

1. Reference Form:

• Formula: =INDEX((A1:C10, E1:G10), 2, 3, 2)
• Looks in the 2nd range (E1:G10) and returns the value in the 2nd row and 3rd column.

Features

• Allows you to extract specific data from large datasets by pinpointing the desired cell.
• Combines well with other functions like MATCH for dynamic lookups.

Tips

1. Dynamic Lookups:

• Combine with MATCH to dynamically find a row or column based on criteria. Example: =INDEX(A1:C10, MATCH(90, A1:A10, 0), 2) finds 90 in the first column and retrieves the corresponding value from the 2nd column.

1. Avoid Errors:

• Ensure the row_num and column_num are within the range specified in the array or reference; otherwise, you'll get a #REF! error.