INDEX

Return a cell value as per row‐and‐column intersection in Excel
Uses an index to choose a value from a reference or array

Rational

Return a cell value as per row‐and‐column intersection.

Description

The Excel INDEX function returns a cell value from one or multiple data range at a given row‐and‐column intersection. This formula can be used to extract a specific value from a large set of data.
INDEX is generally used with MATCH function that finds the relative row and column of a reference.

Syntax

= INDEX(array, row_num, [column_num])
= INDEX(reference, row_num, [column_num], [area_num])

Parameters

array – The table or array to extract data
reference – One or more data range.
row_number – Row number in the data range
column_number [optional] – Column number in the data range
area_number [optional] – Specify the data range to be used.

Return Value

Value of a specific position in a data range

Key Notes

  • The INDEX function is useful to return a set of value based on its location in the worksheet.
  • If a single row or column represents the data table (array), then the corresponding row_num or column_num argument is optional.

There are two ways to use the INDEX function:

Array form
In the array form of INDEX, the first parameter is array, and return based on the intersection of row and column from one cell range.

Reference Form
In the reference form of INDEX, the first parameter is a reference, return based on the intersection of row and column from one or more nonadjacent cell range. Moreover, area_number [optional] indicate which cell range to search.

Example

For Example, to show any number, text in a cell we apply  the formula =INDEX(H7:L12,2,1)  returns  (February) as to get value.

For detail analysis please read MATCH and INDEX blog

Keep Reading Similar Functions:

Returns a reference indicated by a text value

INDIRECT

The INDIRECT function creates a cell reference from a given text string same as the name of a worksheet and extracts value from a specific cell.

Read More »
Return address of a cell in Excel

ADDRESS

The ADDRESS function returns cell address from given row and column numbers of a worksheet. For example, if you pass ADDRESS(5,6) the function returns $F$5.

Read More »
Returns value from a data as per given offset range in Excel

OFFSET

The OFFSET function returns one or multiple data points of a data set by giving a reference point with a specified number of rows and columns to offset.

Read More »

Keep Reading Similar Blog: