Get the value at a given position in a range or array Show
What is the INDEX Function?The INDEX Function[1] is categorized under Excel Lookup and Reference functions. The function will return the value at a given position in a range or array. The INDEX function is often used with the MATCH function. We can say it is an alternative way to do VLOOKUP. As a financial analyst, INDEX can be used in other forms of analysis besides looking up a value in a list or table. In financial analysis, we can use it along with other functions, for lookup and to return the sum of a column. There are two formats for the INDEX function:
The Array Format of the INDEX FunctionThe array format is used when we wish to return the value of a specified cell or array of cells. Formula=INDEX(array, row_num, [col_num]) The function uses the following arguments:
The Reference Format of the INDEX FunctionThe reference format is used when we wish to return the reference of the cell at the intersection of row_num and col_num. Formula=INDEX(reference, row_num, [column_num], [area_num]) The function uses the following arguments:
If the area_num argument is omitted, it defaults to the value 1 (i.e., the reference is taken from the first area in the supplied range). How to Use the INDEX Function in ExcelTo understand the uses of the function, let us consider a few examples: Example 1We are given the following data and we wish to match the location of a value. In the table above, we wish to see the distance covered by William. The formula to use will be: We get the result below: Example 2Now let’s see how to use the MATCH and INDEX functions at the same time. Suppose we are given the following data: Suppose we wish to find out Georgia’s rank in the Ease of Doing Business category. We will use the following formula: Here, the MATCH function will look up for Georgia and return number 10 as Georgia is 10 on the list. The INDEX function takes “10” in the second parameter (row_num), which indicates which row we wish to return a value from and turns into a simple =INDEX($C$2:$C$11,3). We get the result below: Things to Remember
Click here to download the sample Excel file Additional ResourcesThank you for reading CFI’s guide to the INDEX Function. To learn more, check out these additional CFI resources:
Article Sources
What function would you use to search for a certain value in a spreadsheet?The VLOOKUP or Vertical Lookup function is used when data is listed in columns. This function searches for a value in the left-most column and matches it with data in a specified column in the same row. You can use VLOOKUP to find data in a sorted or unsorted table.
What function would you use to search for a certain value in a spreadsheet column to return the corresponding piece of information 1 point search Vlookup return Countif?The Microsoft Excel VLOOKUP function does a vertical lookup for a value in the first column in a table, and returns a value from a different column, in the same row, in that table. VLOOKUP function can find exact matches in the lookup column, such as product code, and return its price.
What function would you use to convert data in a SQL table from one datatype to another?The CONVERT() function in SQL server is used to convert a value of one type to another type. It is the target data type to which the to expression will be converted, e.g: INT, BIT, SQL_VARIANT, etc.
|