Finding the Location of Data with Excel's MATCH Function

Locate the position of a specific value in your data set

add_a_photo Comp Save to Board High Angle View Of Graph Charts With Magnifying Glass
Don't get lost in your data, use the Excel MATCH function.

The Excel MATCH function is used to return a number that indicates the first relative position of data in a list, array, or selected range of cells; it is used when the item's place in the list is needed instead of the item itself.

The MATCH Function Syntax and Arguments

Screenshot of Excel showing the use of the MATCH function.

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 )

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 (required) is the range of cells being searched.

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

Using the MATCH Function in Excel

Screenshot of Excel showing the formula building process for MATCH.

Our MATCH example uses the function to find the position of the term Gizmos in an inventory list. You can either enter the function syntax manually into a cell or use the aide of the Formula Builder as shown here.

To enter the MATCH function and arguments using the Formula Builder:

  1. Begin by filling out your Excel workbook as shown in this tutorial using columns C, D, and E. Leave Cell D2 blank as that particular cell will host our function.
  2. Once all information is filled in, click on cell D2.
  3. Click on the Formulas tab of the ribbon menu.
  4. Choose Lookup and Reference from the ribbon to open the function drop-down list.
  5. Click on MATCH in the list to bring up the Formula Builder.
  6. In the Formula Builder, click on the Lookup_value line.
  7. Click on cell C2 in the worksheet to enter the cell reference.
  8. Click on the Lookup_array line.
  9. Highlight cells E2 to E7 in the worksheet to enter the range.
  10. Click on the Match_type line.
  11. Enter the number on this line to find an exact match to the data in cell D3.
  12. Click Done to complete the function and close builder.
  13. The number 5 appears in cell D3 since the term Gizmos is the fifth item from the top in the inventory list.

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

=MATCH(C2,E2:E7,0)

Combining 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:

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