Excel MATCH Function: Finding the Location of Data

of 01

Excel MATCH Function

Find the Relative Position of Data in a List with the Match Function
Finding the Relative Position of Data with the Match Function. © Ted French

MATCH Function Overview

The MATCH function is used to return a number that indicates the relative position of data in a list or a selected range of cells. It is used when the position of the specified item in a range is needed instead of the item itself.

The specified information can be either text or number data.

For example, in the image above, the formula containing the MATCH function

returns the relative location of Gizmos as 5, since it is the fifth entry in the range F3 to F8.

Likewise, if the range C1:C3 contains the numbers such as 5, 10, and 15, then the formula

would return the number 3, because 15 is the third entry in the range.

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.

The MATCH Function Syntax and Arguments

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) 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) 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. Choices: -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.

Example Using Excel's MATCH Function

This example will use the MATCH function to find the position of the term Gizmos in an inventory list.

Options for entering the function and its arguments include:

  1. Typing the complete function such as =MATCH(C2,E2:E7,0) into a worksheet cell
  2. Entering the function and arguments using the function's dialog box

Using the MATCH Function Dialog Box

The steps below detail how to enter the MATCH function and arguments using the dialog box for the example displayed in the image above.

  1. Click on cell D2 - the location where the results of the function are displayed
  2. Click on the Formulas tab of the ribbon menu
  3. Choose Lookup and Reference from the ribbon to open the function drop down list
  4. Click on MATCH in the list to bring up the function's dialog box
  5. In the dialog box, click on the Lookup_value line
  6. Click on cell C2 in the worksheet to enter the cell reference into the dialog box
  7. Click on the Lookup_array line in the dialog box
  8. Highlight cells E2 to E7 in the worksheet to enter the range into the dialog box
  9. Click on the Match_type line in the dialog box
  10. Enter the number " 0 " (no quotes) on this line to find an exact match to the data in cell D3
  11. Click OK to complete the function and close the dialog box
  12. The number " 5 " appears in cell D3 since the term Gizmos is the fifth item from the top in the inventory list
  13. When you click on cell D3 the complete function =MATCH(C2,E2:E7,0) appears in the formula bar above the worksheet

Finding the Position of Other List Items

Rather than enter Gizmos as the Lookup_value argument, the term is entered into the cell and the cell D2 and then that cell reference is then entered as the argument for the function.

This approach makes it easy to search for different items without having to alter the lookup formula.

To search for a different item - such as Gadgets

  1. Enter the part name into cell C2
  2. Press the Enter key on the keyboard

The result in D2 will update to reflect the position in the list of the new name.