Contents
It happens to you: you look at a person and think “what kind of @#$%)(*?” And then, upon close acquaintance, it turns out that he knows five languages, jumps with a parachute, has seven children and a black belt in chess, and , in general, a kind-hearted person and a smart girl?
So in Microsoft Excel: there are several similar functions, about which the phrase “appearances are deceptive” works 100%. One of the most versatile and useful is the function INDEX (INDEX). Not all Excel users know about it, and even fewer use all its features. Let’s look at the options for its use, because there are already five of them.
Option 1. Extract data from a column by cell number
The simplest use case for the function INDEX – this is a situation where we need to extract data from a one-dimensional column range if we know the ordinal number of the cell. The syntax in this case would be:
=INDEX(range_column; Ordinal_cell_number)
This option is known to most advanced Excel users. In this form, the function INDEX often used in conjunction with the function MORE EXPOSED (MATCH), which gives the number of the searched value in the range. Thus, this pair replaces the legendary VPR (VLOOKUP):
… but, unlike VLOOKUP, they can extract values to the left of the search column and the number of the result column does not need to be calculated.
Option 2: Extract data from a XNUMXD range
If the range is two-dimensional, i.e. consists of several rows and columns, then our function will be used in a slightly different format:
=INDEX(Range; Line_number; Column_number)
Those. the function extracts the value from the range cell at the intersection of the row and column with the given numbers.
It is easy to see that with this variation INDEX and two functions MORE EXPOSED one can easily implement a two-dimensional search:
Option 3: Multiple tables
If there is not one table, but several of them, then the function INDEX can extract data from the desired row and column of the specified table. In this case, the following syntax is used:
=INDEX((Range1;Range2;Range3); Line_number; Column_number; range_number)
Pay special attention to the fact that in this case the first argument – the list of ranges – is enclosed in brackets, and the ranges themselves are listed separated by semicolons.
Option 4: Referencing a column/row
If in the second use of the function INDEX set the row or column number to zero (or simply leave it blank), then the function will no longer return a value, but a link to a range-column or range-row, respectively:
Please note that since INDEX gives in this variant not a specific cell value, but link to range, then to calculate it, you need to enclose it in an additional function, for example SUM (SUM), AVERAGE (AVERAGE) etc.
Option 5: Link to a cell
It is well known that the standard reference to any range of cells in Excel looks like Start-Colon-EndEg A2: B5. The trick is that if we take the function INDEX in the first or second option and substitute it after colon, then our function will no longer produce a value, but an address, and at the output we will get a full reference to the range from the initial cell to the one that it found INDEX:
Something similar can be done with the function DISPOSAL (OFFSET), but, unlike INDEXIs volatile, i.e. recalculated each time any cell in the worksheet changes. INDEX however, it works more finely and starts recalculation only when its arguments change, which significantly speeds up the calculation in heavy books compared to DISPOSAL.
One of the most common application scenarios in practice INDEX in this version, it is a combination with the function COUNT (COUNTA)to get auto-stretching ranges for dropdowns, pivot tables, etc.
- 3D data search across multiple sheets (VLOOKUP XNUMXD)
- Search and lookup by multiple conditions (VLOOKUP by multiple columns)
- Substituting data from one table to another using the VLOOKUP function
SaveSave