Description
Use this function to return a value or the reference to a value from within a table or range. Supported in Chains.
Syntax
INDEX(array, row_num, [column_num])
Inputs
This function accepts the following arguments:
| Name | Required | Description | Valid input |
|---|---|---|---|
array |
Yes | The range of cells or array constant to select from. | A range of cells or an array constant. |
row_num |
Yes | The row number in the array from which to return a value. | A positive integer, or blank if column_num is provided. |
column_num |
Optional | The column number in the array from which to return a value. | A positive integer. Required if array has more than one column. |
Examples
Sample data
The following table contains the data used in the sample formulas below.
| A (Product) | B (Color) | C (Price) | |
|---|---|---|---|
| 1 | Apple | Red | $0.50 |
| 2 | Banana | Yellow | $0.30 |
| 3 | Cherry | Red | $0.75 |
| 4 | Pear | Brown | $0.60 |
Sample formulas
| Use case | Formula | Explanation and Result |
|---|---|---|
| Find a specific value in a table by its row and column number. | =INDEX(A1:C4, 2, 3) |
Looks in the range A1:C4 and returns the value at the intersection of the 2nd row and 3rd column. Result: $0.30 |
| Find a specific value in a single column by its row number. | =INDEX(A1:A4, 3) |
Looks in the single-column range A1:A4 (Product) and returns the value from the 3rd row. Result: Cherry |
| Return a value from a specific column by searching for a value in a different column. | =INDEX(C1:C4, MATCH("Cherry", A1:A4, 0)) |
The MATCH function finds "Cherry" in A1:A4 at position 3. INDEX then returns the 3rd value from the Price column (C1:C4).Result: $0.75 |
| Return all values from a specified column as an array. | =INDEX(A1:C4, 0, 2) |
Using 0 as the row number tells the function to return the entire column. Here, it returns all values from the 2nd column (Color). Result: Red; Yellow; Red; Brown (As cells in a column, starting from the cell the formula is in.) |
Notes
- If
row_numorcolumn_numis 0, INDEX returns an array of the entire row or column. - If
row_numorcolumn_numis out of bounds, INDEX returns a #REF! error. - If an INDEX is used for an array, it will return a #VALUE! error, because Workiva Spreadsheets do not support arrays.
- When using INDEX with a single column or single row, you can omit the
column_numorrow_numargument, respectively.
Tips
- INDEX is often used in combination with MATCH for flexible lookup formulas. The page Using INDEX-MATCH to create flexible lookups provides an example.
- When working with large datasets, INDEX can be more efficient than VLOOKUP for multiple lookups.
- You can use INDEX to dynamically reference entire rows or columns by using "0" as the row or column argument.
- Combine INDEX with other functions such as SMALL or LARGE to find the nth largest or smallest value in a range.