The INDEX function is a
built-in function in Excel that is categorized as a Lookup/Reference Function. The
Excel INDEX function returns the value at a given position in a range or array.
You can use index to retrieve individual values or entire rows and columns.
There are two formats of the
function, which are the Array Format (which is
the most basic format), and the Range Format of
the function. These are described separately below.
Array format
The Array format of the Index
function is used when you want to look up a reference to a cell within a single
range.The syntax of the function is:
INDEX (array, row_num, [col_num])
Where the arguments are as follows:
Array
The specified array or range
of cells.
row_num
Denotes the row number of the
specified array. If set to zero or blank, this defaults to all rows in the
supplied array.
[col_num]
Denotes the column number of
the specified array. If set to zero or blank, this defaults to all columns in
the supplied array.
Note that the row_num and the [col_num] arguments cannot
both be zero or blank.
Example 1
In the following example, the Index function returns a
reference to row 5 of the range C1:C5, which is cell C5. This has the value 8.
Example 2
In the following example, the Index function returns a
reference to row 5 and column 2 of the range C1:D5, which is cell D5. This has
the value 3.
Reference
form
The Range format of the Index
function can be used to extract references from ranges that are made up of more
than one area. The syntax of the function is:
INDEX( range, row_num, [col_num], [area_num]
)
Where the arguments are as follows:
range
The specified array or range
of cells.
Note: If multiple areas are
input directly into the function, the individual areas should be separated by
commas and surrounded by brackets - ie. ( A1:B2, C3:D4, etc).
row_num
Denotes the row number of the
specified area. If set to zero or blank, this defaults to all rows of the
specified area within the supplied range.
[col_num]
Denotes the column number of
the specified area. If set to zero or blank, this defaults to all columns of
the specified area within the supplied range.
[area_num]
If the initial specified range
is made up of more than one area, the [area_num] argument specifies the number
of the area to be used.
(Note that the areas are
numbered by the order they are specified).
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.
Note that either (but not both) of the row_num or the [col_num]
arguments may be zero or blank. If they are both zero or blank, the Index
function will return an error.
Example 1
In the following example, the Index function returns a
reference to row 4 and column 2 of the 1st area in the supplied range. This is
cell D4, which evaluates to the value 5.
Example 2
In the following example, the Index function returns a
reference to row 3 and column 1 of the 3rd area in the supplied range. This is
cell B12, which evaluates to the value 7.
Reference :
follow us on
http://arivilm.blogspot.in
like us on
https://www.facebook.com/Arivilm2501/
No comments:
Post a Comment