The function "INDEX" in Excel: description, application and examples

At the moment, Excel ispopularity is second only to Word. It allows you to easily make a variety of economic and statistical calculations over a large amount of data. For this purpose, it provides a large number of built-in functions, including auxiliary ones. Some of them are capable of performing actions, including on data arrays. These include the function "INDEX". In Excel, it is used both separately and with "MATCH", which will be discussed below.

Description

The "INDEX" function in Excel returns the value (reference to value) of the cell contents specified by the row and column numbers of a table or a named range.

Its syntax is simple and looks like this: INDEX (array, row number, column number).

This function can also work withsingle row or single column. In this case, after specifying a one-dimensional array, one number is set. It denotes the row number if the array is a column, and vice versa.

The function "INDEX" in Excel sometimes gives the value "# REFERENCE!". This most often occurs if the cell located at the intersection of the specified row and column is outside the specified range.

Application examples

Let us consider several cases of using the "INDEX" function in practice.

Suppose there is an array consisting of 4columns and 4 rows (see table). If you enter the expression “= INDEX (B2: E5, 2, 3)” (without the quotes) into one of the cells of the table located outside the A1: E5 range and click on “Enter,” then the value “begonia” will be returned.

If you want to find out how many students in Group 2 have received a rating of “unsatisfactory”, then the following expression should be entered in the appropriate cell: INDEX (C2: C5, 1).

The function "FIND" in Excel

Both examples above will not work.with large amounts of data. The fact is that the use of the "INDEX" function in Excel involves entering the number of a row and column not of the table itself, but of an array of data. It is rather difficult to do when it comes to a large number of elements. Another Excel function can help solve the problem.

Consider the case where the array consists of a single string.

The range of values ​​in this case is B3: B6.

Select a cell in another row, for example, D1.Enter the name of the fruit, the position of which we want to find, in this case “oranges”. In the cell (E1), where we want to write down the number of the corresponding line, enter “= MATCH (D1; B3: B6; 0)” (see table). As a result, the number 3 appears there. It is such a number in the B3: B6 range of the expression “oranges”.

The last 0 means that you want to find an exact match with the value of D1.

How to find all the text values ​​that meet certain criteria

As shown above, the "MATCH" functionreturns only one value (the very first, that is, the upper one). But what to do if there are repetitions in the list. In this case, help the formula of the array. To use them, select the entire data range and use the keyboard shortcut "Ctrl + Shift + Enter". However, its consideration is not the subject of this article.

The "INDEX" and "FIND" function in Excel: examples

Imagine that you need to choose from enoughlarge array only certain data. Consider for simplicity the case with a small number of elements. For example, you have a report on the performance of several groups of students and their assessment. Suppose you want the number of students in the H2 cell to be rated "unsuitable".

It’s best to share bothfunctions. To find out what needs to be entered in H2, we first consider the simplest expression that can be used for this purpose. In particular, the desired value can be obtained by writing to this cell "= INDEX (A2: E5; 1; 2)". Here we used a variant from the previous examples, when the row and column numbers were calculated manually. However, our goal is to automate this process. To do this, instead of the two and the units that indicate the desired row and column, in the array write the corresponding functions "MATCH" issuing these numbers. Please note that we are looking for the expression "beats", located in cell G2 and "c. 2 ”of H2. In addition, we need exact matches, so the last, third, argument in both cases is 0.

Then instead of 1 in the formula INDEX (A2: E5; 1; 2) you should write: MATCH (G2; A2: A5; 0), and instead of 2 - MATCH (H2; A2: E2; 0).

After substitution, we have: INDEX (A2: E5; MATCH (G2; A2: A5; 0); MATCH (H2; A2: E2; 0)). As a result, pressing "Enter", we have the value "10" in this cell.

How to extend the effect of the formula to a certain range

As you know, the function "INDEX" in Excel can be"Stretched" on a certain range. In the example above, these are all 4 cells from H2: J3. In this regard, it is necessary to figure out how to make it so that “stretching” these formulas to the right and down to get the correct values.

The main difficulty is that the arrayA2: E5 has a relative address. To fix this, you should turn it into an absolute. For this, the array is written in the form $ A $ 2: $ E $ 5. The same should be done for both built-in functions, i.e. they should look like MATCH ($ G $ 2; $ A $ 2: $ A $ 5; 0) and MATCH ($ H $ 2; A $ 2: $ E2; 0).

The final form of the formula will be: INDEX ($ A $ 2: $ E $ 5; MATCH ($ G $ 2; $ A $ 2: A $ 5; 0); MATCH ($ H $ 2; $ A $ 2: $ E $ 2; 0)).

As a result, we will have the table below.

To get the correct result you needmake sure that the text values ​​are recorded exactly, including no typos and extra spaces. Otherwise, the program will not consider them as identical.

Now you know how the function is used."INDEX" in Excel. You also know examples of its joint use with “FINANCE”, and you will be able to correctly apply them to solve many practical problems.