Excel: INDEX – Data Search Made Easy

Over time, an Excel table can take on epic proportions. To maintain an overview, however, the program offers lots of practical functions that can help find your way around, and not only when creating mathematical formulas. Functions like VLOOKUP, INDIRECT, or MATCH allow you to work with data efficiently. Excel also features the INDEX function, which reproduces the value of a defined cell. But how is this useful?

In Excel, the INDEX function enables you to reproduce the content of any particular cell in another cell. The formula therefore essentially contains coordinates for this purpose. Based on the column and row specified, the function determines the desired value from an area likewise defined by the user. This can then be shown directly in another cell or used in another function.

Since the INDEX function considers columns and rows, it’s also very useful for work with arrays. In an array, Excel can read out the corresponding value for two indicated factors.

The function uses a clear syntax. The user enters the area to be searched as a parameter and then the coordinates of the desired cell.

The following parameters are used in the function:

• Array – defined by the top-left and bottom-right cell
• Row – in which the desired cell is located
• Column – in which the desired cell is located

The array is always defined using two cells, separated by a colon. If it only contains a single column or a single row, one further parameter suffices. Excel will automatically understand whether you mean the corresponding row or column.

You can either enter the coordinates directly as a number or establish a cell reference. The numbers are then contained in the cells specified. The parameter always relates to the selected area and not to the worksheet, for example. So, if the array begins in cell C2, the D column is number 2 and not number 4.

The INDEX function can also be expanded: It’s possible to directly enter multiple cell areas. This variant referred to as the reference version requires an additional parameter:

The reference comprises a collection of areas. They are entered in curved brackets, separated by a semi-colon. While the row and column parameters remain the same, the last parameter refers to which of the grouped areas you would like to use for your current search.

The sequence in the reference is key here: The area you enter first is also number 1. The areas are counted from left to right – regardless of where the area is located in the worksheet. If you’d like to display the value in another array, you only need to change the area in the last parameter. If this parameter is left out, Excel automatically assumes you mean the first area.

Lastly, INDEX can also be used as a matrix formula. Here, a selected area that contains multiple cells is reproduced in just as many cells. A row or column can be displayed as follows: First, select the corresponding number of empty cells and then enter the INDEX function as normal. (The area has to remain highlighted!) Instead of confirming the entry using the [Enter] key, use the key combination [Ctrl] + [Shift] + [Enter]. The selected area will now appear at the desired position in the table.

The simplest version of the INDEX function is the reproduction of a specific cell’s content. Cell references can be used to make working with the formula a little easier. This way, you can enter the column and row parameters directly in a cell and dynamically adjust the formula.

You can expand the formula if you have multiple tables with the same structure. Assume we have four tables – one for the months of every quarter. However, the structure of the tables is the same: The columns and rows are laid out identically.

The INDEX function only works if you know the corresponding column and row numbers. If they are not numbered clearly, this can present a problem, especially with larger tables. Finding the correct parameters may often take longer than searching for the value itself. Here, it would be easier if you could select the labels on the edge as the criterion, with the function deriving the number of the columns and rows on this basis. To do so, you need to combine INDEX with MATCH in Excel.

The MATCH function searches for a value or term and indicates which cell in a column or row matches the search criterion. The advantage of this combination is that MATCH can search for a term and send the result to INDEX; it then acts as a column or row parameter in the INDEX function.

In Excel, you can also combine INDEX with mathematic or statistical functions such as MEAN. In this case, INDEX (again in conjunction with MATCH) indicates the area for which the mean value is to be calculated. Rather than inserting it directly into the formula, you can dynamically change the selection area via the formula arrangement.