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

Syntax

`MATCH (Lookupvalue, Lookup_Array, [Match_type])`

Parameter

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.

Category

Lookup & Reference

Return

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)