MATCH

MATCH

No Comments

“MATCH”

Used for – “MATCH” function in MS Excel is used to find the relative position of the value within a range.
Syntax – MATCH(lookup_value, lookup_array, [match_type])

             lookup_value – It is any text/number/formula/reference cell, you typed or calculated which needs to be find in a row or column. It is a compulsory field and if you did not fill any value excel will not proceed further.
            lookup_array – This is a vector or you can say a row or a column in which you want to search the value. It is compulsory field without which formula results in #N/A error.
            [match_type] – This is an optional field. As the name indicates it can be used to tell MS Excel that lookup value should be exactly the same or it will be approximate match the lookup value on lower side or upper side. Value in this position can be -1 & 1 for approximate match on upper side and lower side respectively and 0 for exact match.
           
Example – Following example will clear the MATCH function:-

In below table column A, B, & C contains some sales data for particular item in specified cities. Now column G & H contains the MATCH function with different criteria.

A
B
C
D
E
F
G
H
1
City
ITEM
Sale
Qty.




2
New Delhi
Orange
1000
1500

1000
=MATCH(F2,C2:C7,0)

3
New Delhi
Banana
2000
1250




4
Muktsar
Papaya
3000
1000

2500
=MATCH(F4,C2:C7,-1)
=MATCH(1100,D2:D7,-1)
5
Muktsar
Orange
4000
750

2500
=MATCH(F5,C2:C7,0)
=MATCH(1100,D2:D7,0)
6
Chandigarh
Banana
5000
500

2500
=MATCH(F6,C2:C7,1)
=MATCH(1100,D2:D7,1)
7
Chandigarh
Papaya
6000
250





And result you get as under:-

A
B
C
D
E
F
G
H
I
1
City
ITEM
Sale
Qty.





2
New Delhi
Orange
1000
1500

1000
1


3
New Delhi
Banana
2000
1250





4
Muktsar
Papaya
3000
1000

2500
#N/A
2

5
Muktsar
Orange
4000
750

2500
#N/A
#N/A

6
Chandigarh
Banana
5000
500

2500
2
#N/A

7
Chandigarh
Papaya
6000
250






NOTE :- Please do care of following points when you use MATCH :-
·         Lookup_array must be in ascending or descending order to get the approximate match on lower side and upper side respectively.
·         MATCH always works from top to bottom (Similar to VLOOKUP).
·         It also stops at the first value that matches the lookup value in case there are duplicate values in the column (Similar to VLOOKUP).

·         [match_type] have default value of 1 if you left it blank. Here it should be noted that -1 only gives correct results when data is descending order.
INDEX

INDEX

No Comments

“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.
            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.
            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.

Is this article helpful to you?

Amazon