- range
- row_index
- [column_index]
- 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.)
You can use the MATCH function inside of the INDEX function to return an item from a range based on the relative position of a value in a range.
This example references the following sheet information:
| Inventory Item | Amount Ordered | Cost per Item | |
|---|---|---|---|
| 1 | Necklace | 1 | $200 |
| 2 | Earrings | 4 | $75 |
| 3 | Ring | 2 | $160 |
Given the table above, here are some examples of using INDEX and MATCH in a sheet:
| Formula | Description | Result |
|---|---|---|
| =INDEX([Inventory Item]1:[Cost per Item]3,MATCH(Item1,[Inventory Item]1:[Cost per Item]3,0), 3) * [Amount Ordered]1 | Return the cost of the Necklace and multiply it by the amount ordered | $200 |
| =INDEX([Inventory Item]1:[Cost per Item]3, MATCH(Item2, [Inventory Item]1:[Cost per Item]3, 0), 3) * [Amount Ordered]2 | Return the cost of the Earrings and multiply it by the amount ordered | $300 |
| =INDEX([Inventory Item]1:[Cost per Item]3, MATCH(Item3, [Inventory Item]1:[Cost per Item]3, 0), 3) * [Amount Ordered]3 | Return the cost of the Ring and multiply it by the amount ordered | $320 |
The MATCH function determines the row position of the inventory item to return. (The 0 at the end of the MATCH portion of the formula ensures that an exact match is found.) The INDEX function then returns the corresponding cost from the range based on provided row and column indexes. The formula multiplies that cost by the Amount Ordered to determine the total order cost.