The INDEX function in Excel is a powerful tool that allows you to retrieve data from a specified range or array. It can be used in a variety of ways, from finding a specific value in a table to creating dynamic references in complex formulas. In this article, we will explore how to use the INDEX function in Excel, along with three examples that demonstrate its capabilities.
The INDEX function syntax
Before we dive into the examples, let’s take a quick look at the syntax of the INDEX function. The function takes the following form:
INDEX(array, row_num, [column_num])
array: The range or array from which to retrieve the data.row_num: The row number of the cell you want to retrieve within the specified array.column_num: [Optional] The column number of the cell you want to retrieve within the specified array. If omitted, the function will return the entire row specified byrow_num.
Example 1: Using INDEX to retrieve a specific value
Suppose you have a table of data that contains the sales figures of a small business for the year 2022. Here’s what the data might look like:
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Month | Sales | Target | Variance | Percent |
| 2 | Jan | $10,000 | $12,000 | -$2,000 | 7.69% |
| 3 | Feb | $12,000 | $13,000 | -$1,000 | 9.23% |
| 4 | Mar | $15,000 | $14,000 | $1,000 | 11.54% |
| 5 | Apr | $13,000 | $14,000 | -$1,000 | 10.00% |
| 6 | May | $14,000 | $15,000 | -$1,000 | 10.77% |
| 7 | Jun | $16,000 | $16,000 | $0 | 12.31% |
| 8 | Jul | $18,000 | $18,000 | $0 | 13.85% |
| 9 | Aug | $16,000 | $16,000 | $0 | 12.31% |
| 10 | Sep | $15,000 | $15,000 | $0 | 11.54% |
| 11 | Oct | $13,000 | $14,000 | -$1,000 | 10.00% |
| 12 | Nov | $12,000 | $13,000 | -$1,000 | 9.23% |
| 13 | Dec | $11,000 | $12,000 | -$1,000 | 8.46% |
Suppose you want to retrieve the sales figure for the month of May. You can use the INDEX function to retrieve this value as follows:
- Select a cell where you want to display the result (e.g., cell G2).
- Type the following formula:
=INDEX(B2:B13, 6)and press Enter.
The formula will retrieve the value of the cell in the 6th row of the range B2:B13, which corresponds to the sales figure for the month of May.
Example 2: Using INDEX with MATCH to retrieve a value from a dynamic range
Suppose you have a table of sales data for a small business that expands each month as new data is added. Here’s what the data might look like:
| A | B | C | |
|---|---|---|---|
| 1 | Month | Sales | Expenses |
| 2 | Jan | $10,000 | $6,000 |
| 3 | Feb | $12,000 | $7,000 |
| 4 | Mar | $15,000 | $8,000 |
| 5 | Apr | $13,000 | $7,500 |
| 6 | May | $14,000 | $8,000 |
| 7 | Jun | $16,000 | $9,000 |
| 8 | Jul | $18,000 | $10,000 |
| 9 | Aug | $16,000 | $9,500 |
| 10 | Sep | $15,000 | $8,500 |
| 11 | Oct | $13,000 | $8,000 |
| 12 | Nov | $12,000 | $7,500 |
| 13 | Dec | $11,000 | $7,000 |
Suppose you want to retrieve the sales figure for a specific month, but you don’t know the row number because the range expands each month. You can use the INDEX function with the MATCH function to retrieve the value from a dynamic range as follows:
- Select a cell where you want to display the result (e.g., cell G2).
- Type the following formula:
=INDEX(B:B, MATCH(F2, A:A, 0))and press Enter. - In cell F2, type the month for which you want to retrieve the sales figure (e.g., “Jun”).
The formula will retrieve the sales figure for the month of June, even though the range B2:B13 expands each month.
Example 3: Using INDEX with multiple criteria to retrieve a value
Suppose you have a table of data that contains the sales figures of a small business for different products and regions. Here’s what the data might look like:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Region | Product | Month | Sales |
| 2 | East | Product A | Jan | $10,000 |
| 3 | East | Product A | Feb | $12,000 |
| 4 | East | Product B | Jan | $8,000 |
| 5 | East | Product B | Feb | $10,000 |
| 6 | West | Product A | Jan | $7,000 |
| 7 | West | Product A | Feb | $9,000 |
| 8 | West | Product B | Jan | $5,000 |
| 9 | West | Product B | Feb | $6,000 |
Suppose you want to retrieve the sales figure for Product B in the East region for the month of January. You can use the INDEX function with multiple criteria to retrieve the value as follows:
- Select a cell where you want to display the result (e.g., cell G2).
- Type the following formula: `=INDEX(D2:D9, MATCH(1, (A2:A9=”East”) * (B2:B9=”Product B”) * (C2:C9=”Jan”), 0))` and press Ctrl+Shift+Enter.
- The formula uses an array formula with three criteria: (1) the region must be “East”, (2) the product must be “Product B”, and (3) the month must be “Jan”. The MATCH function returns the row number of the first match for the criteria, and the INDEX function retrieves the value from the corresponding row in column D.
Conclusion
The INDEX function is a versatile and powerful tool that allows you to retrieve data from a specified range or array. It can be used in a variety of ways, from finding a specific value in a table to creating dynamic references in complex formulas. By mastering the INDEX function, you can significantly improve your productivity and efficiency in Excel. With the examples provided in this article, you can start using the INDEX function in your own work and take your Excel skills to the next level.
