There are two ways to use the INDEX function:
- If you want to return the value of a specified cell or array of cells, see Array Form;
- If you want to return a reference to a specified cell, see Reference Forms.
- Array form
Returns the value of an element in a table or array, selected by the row number and column number index. Array form is used when the first argument to the function INDEX is an array constant.
grammar
INDEX(array, row_num, [column_num])
The array form of the INDEX function takes the following arguments:
■ Array: Required, cell range or array constant.
If the array contains only one row or column, the corresponding row_num or column_num parameters are optional.
If the array has multiple rows and columns, and only row_num or column_num is used, INDEX returns an array of the entire row or column in the array.
■row_num: Required. Select a row in the array from which the function returns a value. If row_num is omitted, column_num is required.
■column_num: Optionally, select a column in the array from which the function returns a value. If column_num is omitted, row_num is required.
Instructions
■ If both row_num and column_num parameters are used, INDEX returns the value in the cell at the intersection of row_num and column_num.
■row_num and column_num must point to a cell in the array; Otherwise, INDEX returns #REF! Mistake.
■ If row_num or column_num is set to 0 (zero), INDEX returns an array of values for the entire column or row, respectively. To use an array
To return the value of the form, enter the INDEX function as an array formula.
- Reference form
Returns the cell reference at the intersection of the specified row and column. If the reference consists of non-adjacent options, you can select the selection you want to look for.
grammar
INDEX(reference, row_num, [column_num], [area_num])
The reference form of the INDEX function takes the following arguments:
■ Reference: Required, a reference to one or more cell regions.
If you want to enter a non-adjacent area for a reference, enclose the reference in parentheses.
If each zone in the reference contains only one row or column, the row_num or column_num parameters are optional, respectively.
For example, for a single-line reference, you can use the function INDEX(reference,,column_num).
■row_num: Required, the line number of a line in the reference, from which the function returns a reference.
■column_num: Optionally, a reference to a column column. The function returns a reference from this column.
■area_num: Optional. Select a reference range from which the intersection of row_num and column_num is returned.
The number of the first zone selected or entered is 1, the second zone is 2, and so on. If area_num is omitted, INDEX uses region 1.
The areas listed here must be on a worksheet. If the area you specify is not on the same worksheet, it will cause #VALUE! Mistake.
If you need to use areas that are on different worksheets from each other.
It is recommended to use the array form of the INDEX function and use another function to compute the regions that make up the array.
For example, you can use the CHOOSE function to calculate the range to be used.
For example, if the reference description cell (A1: B4, D1: E4, G1: H4)
area_num 1 indicates area A1: B4, area_num 2 indicates area D1: E4, and area_num 3 indicates area G1: H4.
Instructions
■ After reference and area_num select a specific range, row_num and column_num select a specific cell:
row_num 1 is the first row in the zone, column_num 1 is the first column, and so on.
The reference returned by INDEX is the intersection of row_num and column_num.
■ If row_num or column_num is set to 0 (zero), INDEX returns a reference to the entire column or row, respectively.
row_num, column_num, and area_num must point to cells in the reference; Otherwise, INDEX returns #REF! Mistake.
If row_num and column_num are omitted, INDEX returns the reference region specified by area_num.
The result of the function INDEX is a reference and is also interpreted as a reference in other formulas.
The return value of the function INDEX can be used as a reference or a numerical value, depending on the needs of the formula.
For example, the formula CELL(width,INDEX(A1:B2,1,2)) is equivalent to the formula CELL(width,B1).
The CELL function refers to the return value of the function INDEX as a cell.
On the other hand, the formula 2*INDEX(A1:B2,1,2) interprets the return value of the function INDEX as a number in cell B1.
I want to create a clean, concise and efficient office reference manual, in order to maintain a good reference experience, I did not add any third-party advertisements in my blog, of course, if you are willing, you can watch the following video, it can bring me some income, so that I can better maintain the server to provide higher quality service, thank you for your help and support.
You can choose any link below to watch my videos and I will earn some money to strengthen the site and resist DDOS attacks that may happen at any time, thank you for your support!
(Please note: Please do not maliciously click on links or video ads to increase invalid traffic, as this can result in lower or no revenue.)
Learn to consult the manual video
(Will be released in the later period with the consult the manual article, please pay attention.)
My Febspot video list (Learn after-school relaxation videos)
https://suu.us/sponsor
My Febspot video home page
https://www.febspot.com/leet4477/