Excel INDEX function


The INDEX function in Excel returns the value of a cell at a specified row and column within a given range. It’s a powerful and versatile function often used in complex formulas, particularly when combined with MATCH for dynamic lookups. INDEX can be used to retrieve individual values or entire rows and columns from a data set.
					=INDEX(array, row_num, [column_num])
  • array: The range of cells or array constant from which to retrieve the data.
  • row_num: The row number in the array from which to return a value. If set to 0, it returns an array of values for the entire column.
  • [column_num]: [Optional] The column number in the array from which to return a value. If omitted or set to 0, it returns an array of values for the entire row.
Return value
Returns the value or the array of values located at the specified row and column in the array.

How to use

Use INDEX by specifying the range (array), and then defining the row and column from which to retrieve the value. If you only need a specific row or column, set the other parameter (row_num or column_num) to 0.


INDEX for Specific Cell Retrieval
Fetching a Value from a Specific Location: To obtain the value in the third row and second column of a two-dimensional range:
					=INDEX(A1:B5, 3, 2)
Here, the formula looks within the range A1:B5 and retrieves the value located at the intersection of the third row and the second column, which is in cell B3.
INDEX with MATCH for Dynamic Lookup
Dynamic Data Retrieval Using INDEX and MATCH: To dynamically find and return a value from a table based on a specified criterion:
					=INDEX(B2:D10, MATCH("DesiredItem", A2:A10, 0), 2)
Assuming “DesiredItem” is the item you’re searching for in column A, this formula uses MATCH to find the row where “DesiredItem” appears. INDEX then retrieves the corresponding value from the second column in the B2:D10 range.
INDEX and MATCH for Row and Column Lookup
Two-Way Lookup with INDEX and MATCH: To perform a lookup that considers both row and column criteria:
					=INDEX(A1:D10, MATCH("RowCriteria", A1:A10, 0), MATCH("ColCriteria", A1:D1, 0))

This formula uses two MATCH functions – the first to locate the row based on “RowCriteria” and the second to find the column using “ColCriteria”. INDEX then extracts the value at the identified row and column intersection.

INDEX for Entire Row/Column
Retrieving a Whole Row or Column: To extract an entire row or column from a range:
					=INDEX(A1:C10, 4, 0)
=INDEX(A1:C10, 0, 2)

The first formula retrieves the 4th row from the range A1:C10 while the second formula retrieves the 2nd column from the range A1:C10.

INDEX in Array Form
Array Extraction Using INDEX: To extract a subset of an array:
					=INDEX(A1:C5, 0, 2)
This formula retrieves the entire second column from the range A1:C5. If the row_num is set to 0, INDEX returns an array of values for the entire specified column.
INDEX in Reference Form with Multiple Ranges
Selecting From Multiple Ranges: To choose a value from multiple ranges:
					=INDEX((A1:B2, C1:D2), 2, 1, 2)
This formula uses the reference form of INDEX to select from two ranges, A1:B2 and C1:D2. area_num is set to 2, so it selects from the second range (C1:D2), and then returns the value at the intersection of the second row and first column in that range.

Additional Notes

  • INDEX is versatile and can be used in both simple and complex scenarios. It’s particularly powerful when combined with MATCH for dynamic lookups.
  • The ability of INDEX to return entire rows or columns is useful for creating dynamic ranges and extracting arrays of data.
  • Understanding the distinction between the array form and the reference form of INDEX is crucial for leveraging its full potential, especially when dealing with multiple ranges or arrays.

Related Functions

Excel XLOOKUP function

The Excel XLOOKUP function searches for a value, offering flexible matching options and improvements over VLOOKUP and HLOOKUP.

Excel MATCH function

The Excel MATCH function finds the position of a value within a range, essential for dynamic lookups and complex searches.

Excel FILTER function

The Excel FILTER function dynamically isolates data in an array based on criteria, essential for targeted analysis and reporting.

Excel XMATCH function

The Excel XMATCH function returns the position of a value within a range, offering advanced matching and search capabilities.

Excel VLOOKUP function

The Excel VLOOKUP function searches a table's first column for a value, returning a specified column's value from the same row.

Content Navigation