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) |