How to find the position of a value in a data range using the MATCH function

This week’s tip is about the MATCH function and how it can be used to return the relative position of a value within a range. If you are in Sales, you can use the MATCH function to find the position of a sales value within a data range.

The MATCH function searches for a specified item in a range and then returns the relative position of that item in the range. Use MATCH, instead of one of the LOOKUP functions, when you need the position of an item in a range, instead of the item itself.

Note: Download the sample workbook to practice this exercise.

Applies to: Microsoft Excel 2007, 2010 and 2013

The screen shot below will be used for this example. Note that the list is sorted by the Product Sales column, in ascending order.

tip-1

1.     Select cell F20.

2.     Select the Formulas tab and Lookup & Reference as below.

tip-2

3.     Then select MATCH from the drop down list.

tip-3

4.     Enter the formula arguments as shown below.

tip-4

The MATCH function syntax has the following arguments:

  • Lookup_value  Required. The value that you want to match in lookup_array
    • The lookup_value argument can be a value ,cell reference to a number, text, or logical value
  • Lookup_array Required. The range of cells being searched.
  • Match_Type  Optional. The number -1, 0, or 1.

tip-5

5.     Select OK.

6.     To create a drop down list for the values

a)     Select cell F21 and then select the Data tab and Data Validation as shown below.

tip-7

7.     The Data Validation pop-up will appear, In the Settings tab, select the options and input the source as shown below and select OK.

tip-7

8.     A drop down list will be inserted which will allow you to see where a selected amount (value) is positioned.

tip-8