How to use the INDEX function in Excel?

[tongfkymm44]

With the INDEX function in Excel, it is possible to return the contents of a user-defined cell. To do this, the formula contains a series of “coordinates”. The function uses the column and row specifications to determine the desired value from a range also defined by the user. This value can be displayed directly in another cell or combined with another function. Since the result is produced by columns and rows, the INDEX function in Excel is also ideal for working with a matrix , as the program can then extract the corresponding value from two specified factors.

Excel with Microsoft 365 for Business and IONOS
Use Excel to organize your data and save time, included in every Microsoft 365 package!

The function has a very clear syntax. The range you want to search for is entered as a pharmaceuticals email database parameter, followed by the coordinates of the cell you are searching for.

=INDICE(matriz; fila; columna)
The function contains the following data:

Matrix defined by top left and bottom right cells
Row in which the searched cell is located
Column in which the searched cell is located
The range or array is always defined with two cell data separated by a colon. If the array consists of a single column or a single row, it is sufficient to specify only one other argument. Excel automatically understands whether the row or the column is being referred to.

Coordinates are entered as a number or a cell reference is set. The data is always applied relative to the selected range and not to the workbook. That is, if the array starts at cell C2, then column D is the number 2 and not the number 4.

Note
The steps below apply to Microsoft 365 versions of Excel, as well as Excel versions 2021, 2019, and 2016.

INDEX function in practice: examples
The simplest version of the INDEX function is used to display the contents of a specific cell . To make working with the formula less complicated, we use cell references. This way, you can enter data from columns and rows directly into a cell and dynamically adapt the formula.

=INDICE(B2:E10;B12;B13)
In cells B12 and B13 we have entered the two categories of data rows and columns . For this example, it is important to note that the area inside the formula does not include the outer legend (name and year). The result is, in this case, the first entry for Ana (42), which corresponds to row 3 and column 1 of the indicated matrix.

Excel table with simple INDEX function
Quick checks can be done with INDEX and cell references.
If you have several tables with the same structure, you can expand the formula as appropriate . Let's say you have four tables, one for each quarter of the year, in which the structure is always the same: the columns and rows are arranged in the same way.

We have deleted the data from rows, columns and areas using cell references. For easier data entry, drop-down menus can be created to select data. This way, Excel creates a useful form for selecting the correct value with the INDEX function.

Excel INDEX with different tables
With INDEX in Excel you can also include different references.
Microsoft 365 for business with IONOS
The latest versions of your favorite Office apps on any device. Get started with our free setup service.

Exchange with 50 GB mailbox
OneDrive with 1TB of storage
24/7 Expert Support
INDEX formula and other Excel functions
INDEX can also be perfectly combined with other functions, such as AVERAGE . For this purpose, INDEX (in combination with MATCH ) enters the range for which an average figure is to be determined. Instead of entering this directly into the formula, the evaluable range can be changed dynamically.