How to use the INDEX function in excel?
1. Excel Index function as an array
The Index function is an array when the first argument of the function is an array constant.With this function, we have the following formula:
INDEX (Array, Row_num, [Column_num])
Inside:
+ Array: a range of cells or some required array of arrays.
+ Row_num: select rows in the array from which to return a value.
+ Column_num: select the column in the array from which to return a value.
Note readers must have at least one of the two arguments Row_num and Column_num
In the following example, we have the item name and the unit price of each item, find the unit price of the item knowing it's in row 2 column 2
Step 1:
In cell C7, you will enter the formula according to the above syntax, and then we press Enter to perform the Index function
E9: = INDEX (D5: E7,2,2)
Step 2:
Soon we will be returned to the position value E9 corresponding to the ceiling fan unit cost of 235000
2. Excel Index function as a reference
The Index reference function returns the reference of a cell 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])
Inside:
+ Reference: required reference region.
+ Row_num: the row number from which to return a reference, required.
+ Column_num: the column number from which to return a reference, optional.
+ Area_num: number of range will return the value in Reference.If Area_num is omitted, the INDEX function uses region 1, optional.
Step 1:
Also with the above Excel data sheet, we enter the formula as below.Then also press Enter.
Click at cell E9: = INDEX (D5: E7,2,1,1)
Step 2:
The result returned will be the name of the product type 'FAN CEILING'
=> Thus, there are 2 ways to use the INDEX function as array and reference form. Depending on the requirements of each article, the reader has a suitable way to use. Not too hard, right? In addition, you can combine the INDEX function with the MATCH function to be instructed in the following article of TipsMake
You should read it
- Index (INDEX) in SQL
- How to write the above index, below index in Excel
- Look up data in Excel tables: Replace VLOOKUP with INDEX and MATCH
- How to combine Index and Match functions in Excel
- Use Index in Access 2016
- How to use the MATCH function and combine the MATCH function with the INDEX function
- Instructions for indexing on the index on the index in Word
- 10 T-SQL Index statements needed with DBA
May be interested
- How to use the LEN function in Excelthe len function in excel is used to measure the length of a certain string of characters, including spaces and correctly return the total character for the user.
- How to use COUNTIF function on Excelthe countif function on excel is the cell count function to satisfy the given condition, often used in statistics tables.
- How to use the MOD function and QUOTIENT function in Excelinjury in excel has many ways of doing it, can be used manually or using the calculation function.
- 8 little-known Excel functions that can save you a lot of workeven seasoned excel users often find themselves stuck performing tasks manually that could be automated with a few clever functions.
- How to use MID functions to get strings in Excelmid function in excel is a function that takes the middle character string corresponding to the value that the user requires to perform.
- Instructions for creating interactive charts in Excel with INDEX functionexcel makes it easy to create clear, concise charts from your raw data. however, sometimes the graphs create static and not interesting. so the following article will introduce a simple method to help you turn static excel charts into dynamic charts.
- How to use Excel's VALUE functionexcel's value function will convert a string to a digital form, which can be combined with other functions such as left function, right function, mid function.
- Use Index in Access 2016an index is a pointer to each value that appears in the indexed table / column, which has the same meaning as the entries in the index of a book.
- How to use the WRAPROWS function in Excelhaving trouble with handling large data sets in excel? then learn how to use the wraprows function to split them into more manageable rows.
- How to use the IFS function in Excel 2016the ifs function in excel is a logical function introduced in excel 2016. this function is a function that replaces the nested if function and is much easier to use. the ifs function checks one or more conditions and returns a value that meets the first true condition.