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

Currently, Excel is second only to Word in popularity. It allows you to easily carry out a wide variety of economic and statistical calculations on 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 carry out actions, including on data arrays. The "INDEX" function also belongs to them. In Excel, it is used both separately and with SEARCH, which will be described below.

Description

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

Its syntax is simple and looks as follows: INDEX (array, row 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 denotes the row number if the array is a column, and vice versa.

The "INDEX" function in Excel sometimes produces the value "#REF!". 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 several cases of using the INDEX function in practice.

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

If you want to know how many students of Group 2 received a rating of "unsatisfactory", then in the appropriate cell you should enter the expression: INDEX (C2: C5, 1).

SEARCH function in Excel

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

Consider the case when an 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 in it the name of the fruit whose position we want to find, in this case “oranges”. In cell (E1), where we want to write the number of the corresponding line, enter "= SEARCH (D1; B3: B6; 0)" (see table). As a result, the number 3 appears there. It is such a number in the range B3: B6 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 text values ​​that satisfy a certain criterion

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

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

Imagine that you need to select only certain data from a sufficiently large array. For simplicity, we consider the case with a small number of elements. For example, you have a report on the performance of several groups of students and their grades. Suppose you want the number of students to receive an “unsuccessful” grade in cell H2.

To do this, it is best to combine both functions. To find out what you need to enter in H2, first consider the simplest expression that can be used for this purpose. In particular, the desired value can be obtained by writing “= INDEX (A2: E5; 1; 2)” into this cell. 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 two and one, which indicate the desired row and column, in the array write the corresponding functions "SEARCH", issuing these numbers. Please note that we are looking for the expression "ud" located in cell G2 and "gr. 2 ”from H2. In addition, we need exact matches, therefore, in both cases, 0 is indicated as the last, third, argument.

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

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

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

As you know, the function "INDEX" in Excel can be "extended" to 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 sure that by “stretching” this formula to the right and down, to get the correct values.

The main difficulty is that the A2: E5 array has a relative address. To fix this, you should turn it into an absolute. To do 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 SEARCH ($ G $ 2; $ A $ 2: $ A $ 5; 0) and SEARCH ($ H $ 2; A $ 2: $ E2; 0).

The final form of the formula will be: INDEX ($ A $ 2: $ E $ 5; SEARCH ($ G $ 2; $ A $ 2: A $ 5; 0); SEARCH ($ 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 ​​are recorded accurately, including not contain typos and extra spaces. Otherwise, the program will not consider them as the same.

Now you know how to use the INDEX function in Excel. Examples of its joint use with “SEARCH” are also known to you, and you can correctly apply them to solve many practical problems.