Excel MATCH () Function

Excel MATCH () Function

The MATCH () function in excel locates the position of a lookup value in a row, column, or table and returns the relative position of an item in an array.


MATCH (Lookupvalue, Lookup_Array, [Match_type])


Lookupvalue (required)- This parameter represents the value which needs to be looked for in a column, row or table.

Lookup_Array (required)- This parameter refers to the column, table, or table in which we are checking for this value to be present.

Match_type(optional)- This parameter represents takes three values. 1 gives the smallest value position that is greater than, or equal to the value we are searching, 0 gives the value for the exact search, and 1 gives the largest value that is less than or equal to the value we are searching for. The default value is 1.


Lookup & Reference


This function returns the relative position of an item in an array.  

Example 1

Objective: Find the position of 'Lookup Value' given in range B121:B132 and at what position, column 'Product' is in above sample data?

Name Purchased Date Product Category Product # Unit Amount
Sukla Rani 21-Mar-16 Phone HP 15-ay011nr 2 $989
Rahul George 8-Jun-16 Palmtop Pebble Smart Watch 2 $753
Sid Rao 11-Jan-14 Watch Acer Aspire E 15 1 $871
Jenny Zame 16-Jul-15 Alexa Fitbit Blaze Smart Watch 4 $637
William Laslo 31-Mar-14 Watch Nikon D3200 3 $573
Charlie Den 22-Nov-15 Desktop Apple Watch Sport 4 $662
Bing jing 31-May-15 Speaker ASUS F556UA 2 $975
Paul White 8-Jun-16 Laptop Samsung Gear S2 3 $501
Ahmed Bilal 1-Jan-14 Watch ASUS ROG 2 $556
Hans Raj 16-Jun-16 Laptop Nikon D4S 1 $968
Christine 21-Dec-15 Camera Canon ES 1 $600
Look up Value Result Formula
Charlie Den 7 =MATCH(E135,B121:B132,0)
# Unit 5 =MATCH(E137,B121:G121,0)

Example 2

Objective: Pick 'ID' based on the lookup value provided in Lookup value cell.

ID Name Department Salary
651 Sukla Rani Marketing $1,378
774 Rahul George Sales Executive $920
650 Sid Rao Engineering $936
624 Jenny Zame Engineering $806
873 William Laslo CSE $1,218
818 Charlie Den Engineer $1,206
825 Bing jing CSE $1,007
694 Paul White Sales $1,479
604 Ahmed Bilal CSE $866
791 Hans Raj Sales $994
Look up Value Result/ ID Formula
Charlie Den 7 =INDEX(B142:E153,MATCH(C157,C142:C153,0),1)