- range—The group of cells that you want to evaluate.
- row_index—The row position (used in a one-dimensional collection such as a list) of the item to return.
- column_index—[optional]The column position (used in a two-dimensional collection such as a table) of the item to return.
- The range can be a function that returns multiple values, for example: ANCESTORS or CHILDREN.
You can use the COLLECT function inside of the INDEX function to return an item from a range based on the collected values that meet the specified criteria. For example, the formula:
=INDEX(COLLECT([Task Name]:[Task Name], [Assigned To]:[Assigned To], "John", [Not Complete]:[Not Complete], 1), 1)
will return the value Task B. The formula is written to return the task that is assigned to John and is marked as not complete. (See the image below for an example of this formula in action.)