INDEX

The INDEX function in Excel is used to return the value of a cell in a table or an array based on the row and column numbers specified. This function is handy for looking up values within a range of cells or an array.

Syntax

=INDEX(array, row_num, [column_num])

array The range of cells or an array from which you want to retrieve the value.
row_num The row number within the array from which you want to retrieve the value.
column_num (Optional) The column number within the array from which you want to retrieve the value. If omitted the function returns the entire row specified by row_num.

About INDEX

When dealing with tables or arrays in Excel and needing to extract specific data based on row and column references, the INDEX function comes to the rescue. It acts as a versatile tool for pinpointing and fetching values residing in different parts of a table or an array, aiding users in data lookup operations with ease and precision. Utilizing INDEX involves specifying the target array where the desired value is located, indicating the row number that corresponds to the data you wish to retrieve, and optionally specifying the column number if the array is two-dimensional. This function streamlines the process of data retrieval, offering a straightforward solution for accessing information within a structured dataset without hassle or complexity. INDEX is particularly valuable in scenarios requiring dynamic data referencing and extraction from varying locations within a table or array, enhancing the efficiency of data management and analysis tasks.

Examples

Consider a table with student names in column A and their corresponding scores in column B. If you want to retrieve the score for the third student in the list, you can use the INDEX function as follows: =INDEX(A2:B10, 3, 2) This will return the score for the third student in the table.

Imagine an array containing sales data for different regions in rows and various months in columns. If you aim to fetch the sales figure for the Southern region in May, you can employ the INDEX function: =INDEX(C2:H10, 3, 5) This will fetch the sales amount for the Southern region in May from the array.

Questions

Can the INDEX function return values from multiple sheets?

Yes, the INDEX function can retrieve values from multiple sheets within the same workbook. Simply specify the sheet name followed by the cell range in the array argument to access data from different sheets.

What happens if I specify a row or column number outside the defined array range?

If you specify a row or column number that exceeds the dimensions of the array, Excel will return a #REF! error indicating an invalid reference. Ensure that the row and column numbers fall within the array boundaries for accurate data retrieval.

Can the INDEX function be used with non-contiguous ranges?

Yes, the INDEX function can handle non-contiguous ranges by specifying multiple array ranges within the function. Simply separate the ranges by commas within the array argument to retrieve values from different areas across a worksheet.

Is the INDEX function case-sensitive when referencing cell ranges?

No, the INDEX function in Excel is not case-sensitive when referencing cell ranges. You can use a mix of uppercase and lowercase letters in cell references without affecting the function's functionality.

Related functions

MATCH
VLOOKUP
HLOOKUP
OFFSET
LOOKUP
CHOOSE