“INDEX”
Used for – “INDEX” function in MS Excel is
used to find the value from and within a table and range or you can say that
you can find the value in the intersecting cell of “X” column and “Y” row or an
array from the given selection.
Syntax – “INDEX” Function can be used in two different form –
Reference Form and Array Form.
Syntax for Array Form: –
INDEX(array, row_num, [column_num])
array – It is a array from which you wants to find the array or a cell to give the result. It is a compulsory field and if you did not fill any value excel will not proceed further or ends with an error.
array – It is a array from which you wants to find the array or a cell to give the result. It is a compulsory field and if you did not fill any value excel will not proceed further or ends with an error.
row_num – It is a number or cell or a
formula which makes excel to understand which row has to select to return a
value. It is a compulsory field but if row_num is omitted than column_num
becomes the compulsory field without which results turns into error.
[column_num] – It is a number or cell
or a formula which makes excel to understand which column has to select to
return a value. It is a optional field only if row_num has a value but if
row_num is omitted than column_num becomes the compulsory field without which
results turns into error.
Syntax for Reference Form: –
INDEX(refrance, row_num, [column_num], [area_num])
refrance – It is a cell from which you wants to find the array or a cell to give the result. It is a compulsory field and if you did not fill any value excel will not proceed further or ends with an error.
refrance – It is a cell from which you wants to find the array or a cell to give the result. It is a compulsory field and if you did not fill any value excel will not proceed further or ends with an error.
row_num – It is a number or cell or a
formula which makes excel to understand which row has to select to return a
value. It is a compulsory field but if row_num is omitted than column_num
becomes the compulsory field without which results turns into error.
[column_num] – It is a number or cell
or a formula which makes excel to understand which column has to select to
return a value. It is a optional field only if row_num has a value but if
row_num is omitted than column_num becomes the compulsory field without which
results turns into error.
[area_num] – It is a number or cell or
a formula which makes excel to understand which area has to select to return a
value. It is a optional field only if referance has a single value but if reference
has more than one value than area_num becomes the compulsory field without
which results turns into error.
Example – Following example will clear
the INDEX function:
In below table column A, B, C, D
& E contains some sale data of fruit in different cities and column G
contains the “INDEX” function with different ways.
A

B

C

D

E

F

G

H

I


1

Delhi

Chennai

Kolkata

Mumbai


2

Banana

200

300

350

400

=INDEX(A1:E6,2,4)


3

Apple

150

300

250

350

=INDEX(A1:E6,4,2)


4

Pears

500

450

400

475

=INDEX((B2:C6,D2:E6),2,4,1)


5

Papaya

375

300

425

400

=INDEX((B2:C6,D2:E6),4,2,1)


6

Mango

250

300

325

350

=INDEX((B2:C6,D2:E6),4,2,2)


7

=SUM(INDEX(A1:E6,2,0))

And
result you get as under:
A

B

C

D

E

F

G

H

I


1

Delhi

Chennai

Kolkata

Mumbai


2

Banana

200

300

350

400

350


3

Apple

150

300

250

350

500


4

Pears

500

450

400

475

#REF!


5

Papaya

375

300

425

400

300


6

Mango

250

300

325

350

400


7

1250

NOTE
: Please
note down following points to be taken care while using INDEX function:
 In case both row_num & column_num arguments were provided it returns the value of intersection of both.
 In case the row_num is set as 0 then the result will come as array form and giving the value of column_num and vice versa. To get array result type the formula and press Ctrl+Shift+Enter.
 In case either of row_num/column_num/area_num is not pointing the value within array the result will become #REF! error.
 Result of INDEX function can be used as a reference or value in other function depending upon the function in which it is being used. See example.
Dear readers, after reading the Content please ask for advice and to provide constructive feedback Please Write Relevant Comment with Polite Language.Your comments inspired me to continue blogging. Your opinion much more valuable to me. Thank you.