The INDEX function returns a value in a table or region or a reference to a value. The function INDEX() has two forms: an array form and a reference form. Array forms usually return a numeric or numeric array; the reference form usually returns a reference. Returns a reference to a cell at a specific row and column intersection. If the reference is made up of non-contiguous selected regions, you can select the selected region to use as the lookup range. Syntax: INDEX(array, row_num, column_num) returns the value of the specified cell or array of cells in the array. INDEX(reference,row_num,column_num,area_num) returns a reference to the specified cell or range of cells in the reference. Parameters: Array is the cell range or array constant; Row_num is the row number of a row in the array, and the function returns the value from the row. If row_num is omitted, there must be a column_num; Column_num is the column number of a column in the array, and the function returns the value from the column. If column_num is omitted, there must be a row_num. Reference is a reference to one or more cell regions. If you enter a discontinuous selection for a reference, you must enclose it in parentheses. Area_num is an area in the selection reference and returns the intersection area of ​​row_num and column_num in the area. The first area selected or entered has a sequence number of 1, the second is 2, and so on. If area_num is omitted, the INDEX function uses area 1 Example: If A1=68, A2=96, A3=90, the formula “=INDEX(A1:A3,1,1)†returns 68. The INDEX function returns a value or a reference to a value. Used in conjunction with other functions such as the MATCH function, you can construct powerful formulas. The INDEX function can return a value or a reference to a value, so you can use this function to: find the sales for the selected month; get a reference to the specified row, column, and region; create a dynamic region based on the given number; sort alphabetically Text column. The syntax of the INDEX function The INDEX function has two syntax forms - an array and a reference. Use array form, return value; use reference form, return reference. The syntax of the array form is as follows: INDEX(array, row_num, column_num) Array is an array constant or a range of cells If the array has only 1 row or column, then the corresponding row/column number parameter is optional. If the array is larger than 1 row or column, and only use row_num or column_num, return an array of entire rows or columns If row_num is omitted, column_num is required If column_num is omitted, row_num is required Returns the value in the cell where row_num and column_num intersect if the row_num and column_num parameters are all used. If row_num or column_num is zero, return an array of values ​​for the entire column or the entire row The syntax of the reference form is as follows: INDEX(reference,row_num,column_num,area_num) Reference can reference one or more cell regions - enclose non-contiguous regions in parentheses If there is only 1 row or 1 column in each region in the reference, the corresponding row/column parameter is optional. Area_num selects the region in the reference from which to return the value at the intersection of the row and column If you ignore the area_num parameter, use area 1 If row_num or column_num is 0, return a reference to the entire column or the entire row The result is a reference that can be used in other functions INDEX trap If row_num and column_num do not point to a cell in an array or reference, the INDEX function returns #REF! error. Example 1: Find sales for the selected month Enter the line number and the INDEX function returns the sales volume of the location in the reference. The number here is 4, so it returns to April sales. =INDEX($C$2:$C$8,F2) To make this formula more flexible, you can use the MATCH function to return the line number based on the month selected from the drop-down list. =INDEX($C$2:$C$8,MATCH($F$2,$D$2:$D$8,0)) Example 2: Get a reference to the specified row, column, region In this example, there is a named area called MonthAmts, which consists of three non-contiguous areas. The MonthAmts area has 3 blocks - one per month - and there are 4 rows and 2 columns in each block. Here is the naming formula for the MonthAmts name: = 'Ex02'! $B$3: $C$6, 'Ex02'! $E$3: $F$6, 'Ex02'! $H$3: $I$6 Use the INDEX function to return the cost or revenue for a given region and month. =INDEX(MonthAmts,B10,C10,D10) You can multiply the result of the INDEX function, such as the tax calculation in cell F10: =0.05*INDEX(MonthAmts, B10, C10, D10) Alternatively, use the CELL function to return a reference, showing the address of the cell in which the result is located, such as the formula in cell G10: =CELL("address", INDEX(MonthAmts, B10, C10, D10)) Example 3: Creating a dynamic region based on a given number Dynamic regions can be created using the INDEX function. In this example, the named area named MonthList has been created using the following formula: ='Ex03'! $C$1: INDEX('Ex03'!$C:$C,COUNTA('Ex03'!$C:$C)) If another month is added to column C, it will automatically be displayed in cell F2 using MonthList as the data source in the data validation drop-down list. Example 4: Sorting text columns alphabetically In the last example, the INDEX function is used in combination with several other functions to return a list of months in alphabetical order. The COUNTIF function shows how many month names are before the specified month name. The SMALL function returns the smallest item in the list, and the MATCH function returns the line number of the month. The formula is an array formula, so press Ctrl+Shift+Enter after typing. =INDEX($C$4:$C$9,MATCH(SMALL( COUNTIF($C$4:$C$9,"""&$C$4:$C$9), ROW(E4)-ROW(E$3)), COUNTIF($C$4:$C$9,"""&$C$4:$C$9),0)) The Main Distribution Frame is offered with compact size from 10pair (for 1 piece 10 pair disconnection krone module) to 2400 pair. For the installation, there are surface mount, floor standing, pole mount three ways for different application. Outdoor and indoor type is on option. Typically, the outdoor box is equipped with an airtight and watertight cover that allows for easy access to the interior components, making it easy to inspect and replace those components when and as necessary.
For different application and clients, we have the Main Distribution Frame made of ABS, PC, SML and CRC sheet with powder coating to match up with different requirements for indoor and outdoor installation. Besides, we have other MDF(Main Distribution Frame) made of stainless steel for 690pair/1200pair/ 1380pair and 2400pair.
Telecommunication Cabinet, Distribution Open Racks, Krone Module Distribution Box, Outdoor Distribution Box, Cross Connection Box, SMC Distribution Box, Power Distribution Box, Electrical Distribution Box, Optical Distribution Box NINGBO YULIANG TELECOM MUNICATIONS EQUIPMENT CO.,LTD. , https://www.yltelecom.com
Index function syntax description and application examples
INDEX function