Excel INDEX function how to use it?
When using the VLOOKUP function, find the value must be located in the leftmost region of the search. When looking for the value of the column is not in the search area of the leftmost, you can not use the VLOOKUP function to find. In this case, you can use the INDEX function. Next, let us learn how to use the INDEX function in Excel!
Excel INDEX function Usage:
A, INDEX function Usage
INDEX function is to return to the text string specified by the reference and display its contents.
1. Syntax structure
INDEX function has two forms, one is an array form, the other is a reference form. Array form of the syntax structure is as follows: INDEX (& lt; array & gt;, & lt; the number of rows & gt;, [the number of columns]) reference form of the syntax structure is as follows: INDEX (& lt; references & gt;, & lt; the number of rows & gt;, [the number of columns], [region number])
INDEX function is a lookup function to find a reference to a specified string and display its contents. the <array> or <reference> region to find <number of rows> and <number of columns> intersection of the value or reference and return. If not found, then return the error value # N / A.
< array >: an array constant or cell area reference, indicating the scope of the search. <Reference>: cell area reference. & lt; number of rows & gt;: relative to the region of the first line. [Number of columns]: relative to the region of the first column. [region number]: If the reference to more than one region, you need to specify the region number, indicating in which region to find.
4. Application scenarios
INDEX function is often used in conjunction with the MATCH function in order to achieve the goal of dynamic search. the return value of the MATCH as the INDEX function of the second or third parameter to use.
Open “INDEX function.xlsx” file. The following is an example of the name to find out the department and job information to explain the INDEX function. Second, the INDEX function example
Use the INDEX function to present all the names in the form
Shown below, here first need to use the INDEX function to present all the names in the form of A column.
1. In the open material file, select cell A14, in which enter “= INDEX ($H$2:$H$9,1)”.
TIPS: Step 01 in the role of the formula is to return to the H2: H9 cell area in the first value.
2. Press [Enter] key, cell A14 can be displayed in the “Zhang San”.
3. Fill down to cell A21, you can see all the cells are displayed in the “Zhang San”.
TIPS: the second parameter “1” in the filling will not change.
4. If you want to show all the names, you need to modify the A15: A21 cell region of the formula for the second parameter “2 ~ 8”.
5. In the C column you can see that has been set up a set of 1 to 8 of the sequence, you can change the INDEX function of the second parameter into the form of a reference, here in cell B14 enter the formula “= INDEX ($H $ 2: $H $ 9, C14)”, press [Enter] key, you can in B14 cell display “Zhang San”.
6. Fill to cell B21 again, you can display all the names in the B column.
If you delete the auxiliary column by mistake, it is easy to cause calculation errors, you can use the ROW function to achieve the function of the auxiliary column.
TIPS: ROW function is to return a reference to the line number.
its syntax structure: ROW ([reference])
parameter reference for the need to get the line number of cells or cells in the region, can be empty, empty when the function shows the current line of the line number.
reference of the outside of a pair of “”, the role of the square brackets is to illustrate that the parameters are optional, in view of Excel help, where the parameters of the introduction of “” appears, that is, the parameters for the optional parameters.
such as in E14: E21 cell region enter the formula “= ROW ()”, you can see the cell will show the current line of the line number 14 ~ 21.
In cell F14, enter the formula “= ROW (A1)” and fill in to the F21 cell, you can see that it will show A1 ~ A8 line number 1 ~ 8.
This can be combined with the use of INDEX and ROW function will show all the names.
In the open material file, select cell G14, enter “= INDEX ($ H $ 2: $ H $ 9, ROW (A1))”, press [Enter] key that is, in cell G14 shows “Zhang San”.
Double-click the lower-right corner of cell G14 fill handle, complete the fill, you can display all the names.
index in python
1, index function: used to find a value from the list of the first matching index position.
2, index method syntax: list.index(x[, start[, end]]).
3. Parameters: x – the object to find. start – optional, the start position of the search. end – optional, the end position of the search.
4, return value: the method returns the index position of the object to find, if the object is not found then throw an exception.
Code: str1 = “this is string example…. .wow!!!” ; str2 = “example”.
The index function is print(str1.index(str2)).
How does the index function work in python?
The index method in Python detects whether the substring str is included in the string, and if the beg (start) and end (end) ranges are specified, it checks whether it is included in the specified ranges, which is the same as the pythonfind method except that an exception is reported if str is not in the string.
The index function is generally used to retrieve arguments in a sequence and return the index of the first occurrence, and will report an error if it is not found.
EXCEL INDEX how to use
Provide detailed parameters and examples
Excel version reference: 2010
1, Function: return to the table or region of the value or value of the reference
2, Syntax: INDEX (array, row_num, [column_num])
3, parameter description:
Array Required. Cell region or array constants.
If the array contains only one row or column, the corresponding parameter row_num or column_num is optional.
If the array has multiple rows and columns, but only row_num or column_num is used, the function INDEX
returns an entire row or column in the array and the return value is also an array.
Row_num Required. Selects a row in the array from which the function returns a value. If row_num is omitted, then
column_num is required.
Column_num Optional. Selects a column in the array from which the function returns a value. If
column_num is omitted, row_num is required.