INDEX Function and Examples of Its Use in Excel Table

The INDEX function is designed to retrieve values from Excel tables based on their coordinates. It is especially convenient to use when working with databases. This function has several analogs, such as MATCH, VLOOKUP, HLOOKUP, and LOOKUP, which can combine well in complex formulas. However, the flexibility and simplicity of this function come first.



How does the INDEX function work in Excel?

Suppose we are working with a large data table with many rows and columns. The first row of this table contains column headers, and the first column contains row headers. An example of such a table is shown below:

Database.

The task is as follows: determine which numerical value corresponds to a specific Section and a specific article. In other words, we need to get the value of the cell at the intersection of a specific column and row.

Step-by-Step Guide to Using the INDEX Function in Excel

  1. Below the table, create a small helper table to manage the search for values:
    2. Helper table.
  2. In cell B12, enter the number of the desired Section, which will later serve as the criterion for the search query. For example, 3.
  3. In cell B13, enter the number of the article. For example, 7.
  4. In cell B14, enter the following formula:
=INDEX(B2:G10, B13, B12)

As a result, we get the value at the intersection of column 3 and row 7:

Value retrieval using INDEX.

Download INDEX Function Example File in Excel download file

As you can see, the value 40 is at the coordinates Section No. 3 and Article No. 7. The INDEX function does not consider the row numbers of the Excel sheet, only the rows and columns of the table in the range B2:G10.

Explanation of the Example of How the INDEX Function Works

The first argument of the function specifies the range of cells in the table where the search for values at the intersection of rows and columns will be performed. The second argument specifies the row number, and the third argument specifies the column number. Based on this information, the function searches for the corresponding value in the table.

Note! For the INDEX function, the row and column numbers determine the height and width of the table specified in its first argument. They are not related to the row numbers of the Excel worksheet and do not need to match them.