Function "INDEX" in Excel: description, application and examples

Currently, the Excel program is second only to Word. It allows you to easily perform a wide 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 able to perform actions, including over data arrays. This includes the function "INDEX". In Excel, it is used both separately and with "MATCH", which will be described below.

Description

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

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

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

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

Application examples

Let's consider some cases of using the function "INDEX" in practice.

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

If you want to know how many students in Group 2 have received an "unsatisfactory" grade, you should enter the following expression in the appropriate cell: INDEX (C2: C5, 1).

"MATCH" function in Excel

Both of the examples above will not work with large datasets. The fact is that using the "INDEX" function in Excel involves entering the row and column numbers not of the table, but of the data array. This is quite difficult to do when it comes to a large number of elements. One more Excel function can help to solve the problem.

Consider the case where an array consists of a single row.

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

We select a cell in another line, for example D1. We enter into it 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, there appears the number 3. It is this number in the range B3: B6 in the expression "oranges".

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

How to find all text values that satisfy a certain criterion

In the form presented above, the function "MATCH" returns only one value (the very first, i.e., the upper one). But what if there are repetitions on the list? In this case, the array formulas help. To use them, select the entire range of data and use the "Ctrl + Shift + Enter" key combination. However, its consideration is not the subject of this article.

Function "INDEX" and "SEARCH" in Excel: examples

Imagine that you need to select from a large enough array only certain data. Let us consider for simplicity the case with a small number of elements. For example, you have a report on the progress of several groups of students and their evaluation. Suppose you want the number of students in the H2 cell to get the "bad" rating.

For this, it is best to share both functions. To find out what you need to enter in H2, first consider the simplest expression that you can use for this purpose. In particular, the desired value can be obtained if we write "= INDEX (A2: E5; 1; 2)" in this cell. " Here we used the 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 ones that point to the searched row and column, write the appropriate "MATCH" functions in the array that issue these numbers. Note that we are looking for the expression "ud" located in cell G2 and "gr. 2 "from H2. In addition, we need exact matches, so the last, third argument in both cases is 0.

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

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

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

As you know, the function "INDEX" in Excel can be "stretched" to a certain range. In the example considered above, these are all 4 cells from H2: J3. In this connection, it is necessary to find out how to do so, by "stretching" this formula to the right and down, to obtain the correct values.

The main difficulty is that the array A2: E5 has a relative address. To fix this, you should turn it into an absolute one. For this purpose, the array is written in the form $ A $ 2: $ E $ 5. The same should be done for both built-in functions, that is, they should look like SEARCH ($ 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; SIGNALS ($ G $ 2; $ A $ 2: A $ 5; 0); SEARCHS ($ H $ 2; $ A $ 2: $ E $ 2; 0)).

As a result, we will have the table shown below

To get the correct result, you need to make sure that the text values were written accurately, including that they did not contain typos and extra spaces. Otherwise, the program will not treat them as the same.

Now you know how to use the "INDEX" function in Excel. Examples of its sharing with "MATCH" are also known to you, and you will be able to apply them correctly for solving many practical problems.