Share this article:

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 by row_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:

ABCDE
1MonthSalesTargetVariancePercent
2Jan$10,000$12,000-$2,0007.69%
3Feb$12,000$13,000-$1,0009.23%
4Mar$15,000$14,000$1,00011.54%
5Apr$13,000$14,000-$1,00010.00%
6May$14,000$15,000-$1,00010.77%
7Jun$16,000$16,000$012.31%
8Jul$18,000$18,000$013.85%
9Aug$16,000$16,000$012.31%
10Sep$15,000$15,000$011.54%
11Oct$13,000$14,000-$1,00010.00%
12Nov$12,000$13,000-$1,0009.23%
13Dec$11,000$12,000-$1,0008.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:

  1. Select a cell where you want to display the result (e.g., cell G2).
  2. 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:

ABC
1MonthSalesExpenses
2Jan$10,000$6,000
3Feb$12,000$7,000
4Mar$15,000$8,000
5Apr$13,000$7,500
6May$14,000$8,000
7Jun$16,000$9,000
8Jul$18,000$10,000
9Aug$16,000$9,500
10Sep$15,000$8,500
11Oct$13,000$8,000
12Nov$12,000$7,500
13Dec$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:

  1. Select a cell where you want to display the result (e.g., cell G2).
  2. Type the following formula: =INDEX(B:B, MATCH(F2, A:A, 0)) and press Enter.
  3. 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:

ABCD
1RegionProductMonthSales
2EastProduct AJan$10,000
3EastProduct AFeb$12,000
4EastProduct BJan$8,000
5EastProduct BFeb$10,000
6WestProduct AJan$7,000
7WestProduct AFeb$9,000
8WestProduct BJan$5,000
9WestProduct BFeb$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:

  1. Select a cell where you want to display the result (e.g., cell G2).
  2. 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.
  3. 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.