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 | B | C | ||
|---|---|---|---|---|
| Product | Color | 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(A2:C5, 2, 3) |
Looks in the range A2:C5 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(A2:A5, 3) |
Looks in the single-column range A2:A5 (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(C2:C5, MATCH("Cherry", A2:A5, 0)) |
The MATCH function finds "Cherry" in A2:A5 at position 3. INDEX then returns the 3rd value from the Price column (C2:C5).Result: $0.75 |
| Return all values from a specified column as an array. | =INDEX(A2:C5, 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. - Using an INDEX in an array context returns a #VALUE! error because Workiva Spreadsheets don't support arrays (this is different from Excel/Sheets, where INDEX can be used inside array formulas).
- 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.