Index function in Excel Used quite a lot in spreadsheets today. Below are detailed instructions How to use the Index function in Microsoft Excel.
Excel is the most used spreadsheet software today. It brings users many great features, most notably smart data calculation functions. Index is one of them.
The network administrator has compiled for readers the most basic functions in Excel, as well as instructions on how to use the functions: searching data with the Vlookup function, IF conditional function, SUM function… These are the functions. The most basic thing you will encounter when working with Excel files, helping us complete statistical tables and calculate our data.
Today, we will introduce to you the Index function, which is also commonly used in Excel. The Index function is a function that returns an array, helping to retrieve values in a certain cell at the intersection of a column and row. To better understand how to implement the Index function in Excel, please follow our article below.
The Index function in Excel returns the value at the provided position in a range or array. You can use INDEX to retrieve single values or entire rows and columns. The MATCH function is often used with INDEX to provide row and column numbers.
- Purpose: Valid in a position-based list or table.
- Return value: Value at the provided position.
- Argument:
- Array – A range of cells or array constant.
- Row_num: Cell position in reference or array.
- col_num – (optional): Column position in reference or array.
- area_num – [optional]: The range in the reference can be used.
- General formula:
=INDEX(array, row_num, [col_num], [area_num])
1. Array Index function in Excel
The array Index function is used in the case if the first argument of the function is an array constant. The array INDEX function has the following syntax:
=INDEX(Array,Row_num,[Column_num])
In there:
- Array: cell range or a certain row of array numbers required.
- Row_num: select row in array from which a value is returned.
- Column_num: selects the column in the array from which a value is returned.
Readers should note that at least one of the two arguments Row_num and Column_num is required.
Excel table 1: We have a list of fabric types, find the fabric name and know that fabric in row 2, column 2.
In cell C7 you will enter the formula above according to the syntax below, and then press Enter to execute the Index function.
=INDEX(B2:C6,2,2)
Immediately after that we will be returned the value of position A2 corresponding to the Raw Fabric type.
2. Excel Index function as reference
The Index function returns the reference of the cell located at the intersection of a specific row and column. We have the reference Index formula as follows:
=INDEX(Reference,Row_num,[Column_num],[Area_num])
In there:
- Reference: required reference area.
- row_num: row index from which to return a reference, required.
- Column_num: column index from which to return a reference, optional.
- Area_num: the number of the cell range that will return the value in Reference. If Area_num is omitted, the INDEX function uses area 1, optional.
Also with the Excel data table above, we enter the formula as below. Then also press Enter. Click in cell C7 and enter the following formula:
=INDEX(B2:C6,2,1,1)
The returned result will be the name of the Raw Fabric item in line B2.
Things to note
- After reference and area_num have selected a specific range, row_num and column_num select a specific cell: row_num 1 is the first row in the range, column_num 1 is the first column, etc. The reference returned by INDEX is the intersection of row_num and column_num.
- If row_num or column_num is set to 0, INDEX returns a reference to the entire column or row, respectively.
- Row_num, column_num and area_num must point to a cell in the reference; otherwise, INDEX returns a #REF error. If row_num and column_num are omitted, INDEX returns the area in the reference specified by area_num.
- The result of the INDEX function is a reference and is interpreted as such by other formulas. Depending on the formula, the return value of INDEX can be used as a reference or an rvalue. For example, the formula CELL(“width”,INDEX(A1:B2,1,2)) is equivalent to CELL(“width”,B1). The CELL function uses the return value of INDEX as a cell reference. In other words, a formula like 2*INDEX(A1:B2,1,2) converts the return value of INDEX to a number in cell B1.
Thus, we have shown you how to use the INDEX function in array and reference form. The Index function can refer to any cell in Excel, and it's not too difficult to do. You can use this function and combine it with other functions in Excel for more effective use in spreadsheet data.
To be able to calculate data in Excel and draw statistical tables more easily and quickly, you should also know Excel shortcuts or ways to handle Excel files when problems occur.
Refer to the following articles for more information:
Wishing you success!