MATCH

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

• Tweet
• Share
• Share
• Share
• Share