Here’s how to use Excel’s MATCH function to locate the position of a value in a row, column, or table. This is helpful when you must find the item’s place in the list instead of the item itself.

  • The MATCH function syntax is =MATCH(Lookup_value,Lookup_array,Match_type).Can be entered manually. Or, to use Excel’s built-in functions, select Formulas > Lookup & Reference > MATCH.Returns a number that indicates the first relative position of data in a list, array, or selected range of cells.

MATCH Function Syntax

A function’s syntax refers to the layout of the function and includes the function’s name, brackets, comma separators, and arguments. The syntax for the MATCH function is:

=MATCH(Lookup_value,Lookup_array,Match_type)

MATCH Function Arguments

Any input you give to a function is called an argument. Most of the functions found in Excel require some input or information in order to calculate correctly. 

These are the MATCH function’s arguments:

Lookup_value

Lookup_value (required) is the value that you want to find in the list of data. This argument can be a number, text, logical value, or a cell reference.

Lookup_array

Lookup_array (required) is the range of cells being searched.

Match_type

Match_type (optional) tells Excel how to match the Lookup_value with values in the Lookup_array. The default value for this argument is 1. The choices are -1, 0, or 1.

  • If Match_type equals 1 or is omitted, MATCH finds the largest value that is less than or equal to the Lookup_value. The Lookup_array data must be sorted in ascending order.If Match_type equals 0, MATCH finds the first value that is exactly equal to the Lookup_value. The Lookup_array data can be sorted in any order.If Match_type equals -1, MATCH finds the smallest value that is greater than or equal to the Lookup_value. The Lookup_array data must be sorted in descending order.

How to Use the MATCH Function in Excel

The MATCH example shown in this tutorial uses the function to find the position of the term Gizmos in an inventory list. The function syntax can be entered manually into a cell or by using Excel’s built-in functions, as shown here.

To enter the MATCH function and arguments:

  • Open a blank Excel worksheet and enter the data in columns C, D, and E, as shown in the image below. Leave cell D2 blank, as that particular cell will host the function.
  • Select cell D2 to make it the active cell.
  • Select the Formulas tab of the ribbon menu.
  • Choose Lookup & Reference to open the Function drop-down list.
  • Select MATCH in the list to open the Function Arguments dialog box. (In Excel for Mac, the Formula Builder opens.)
  • Place the cursor in the Lookup_value text box.
  • Select cell C2 in the worksheet to enter the cell reference.
  • Place the cursor in the Lookup_array text box.
  • Highlight cells E2 to E7 in the worksheet to enter the range.
  • Place the cursor in the Match_type text box.
  • Enter the number 0 on this line to find an exact match to the data in cell D3.
  • Select OK to complete the function. (In Excel for Mac, select Done.)
  • The number 5 appears in cell D3 since the term Gizmos is the fifth item from the top in the inventory list.
  • When you select cell D3, the complete function appears in the formula bar above the worksheet.
  • =MATCH(C2,E2:E7,0)

Combine MATCH With Other Excel Functions

The MATCH function is usually used in conjunction with other lookup functions, such as VLOOKUP or INDEX and is used as input for the other function’s arguments, such as:

Open a blank Excel worksheet and enter the data in columns C, D, and E, as shown in the image below. Leave cell D2 blank, as that particular cell will host the function.

Select cell D2 to make it the active cell.

Select the Formulas tab of the ribbon menu.

Choose Lookup & Reference to open the Function drop-down list.

Select MATCH in the list to open the Function Arguments dialog box. (In Excel for Mac, the Formula Builder opens.)

Place the cursor in the Lookup_value text box.

Select cell C2 in the worksheet to enter the cell reference.

Place the cursor in the Lookup_array text box.

Highlight cells E2 to E7 in the worksheet to enter the range.

Place the cursor in the Match_type text box.

Enter the number 0 on this line to find an exact match to the data in cell D3.

Select OK to complete the function. (In Excel for Mac, select Done.)

The number 5 appears in cell D3 since the term Gizmos is the fifth item from the top in the inventory list.

When you select cell D3, the complete function appears in the formula bar above the worksheet.

=MATCH(C2,E2:E7,0)

  • The col_index_num argument for VLOOKUP.The row_num argument for the INDEX function.

Get the Latest Tech News Delivered Every Day