Excel > Functions

MATCH Function

The MATCH function returns the position of an item within a range of cells

The syntax for the MATCH function is:

=MATCH(lookup_value, lookup_array, [match_type])

Argument	Purpose
lookup_value	The value you want to search for in the lookup_array
lookup_array	The range of of cells that contains the lookup_value you are searching for
match_type	How to match the lookup_value with values in the lookup_array Can be entered as -1, 0 or 1 and is optional. If omitted Excel assumes match_type to be 1 1 - Finds the largest value less than or equal to the lookup_value. The range of cells should be sorted in ascending order 0 - Finds the first value equal to the lookup_value -1 - Finds the smallest value that is larger than or equal to the lookup_value. The range of cells should be sorted in descending order

Argument

Purpose

lookup_value

The value you want to search for in the lookup_array

lookup_array

The range of of cells that contains the lookup_value you are searching for

match_type

How to match the lookup_value with values in the lookup_array

Can be entered as -1, 0 or 1 and is optional. If omitted Excel assumes match_type to be 1

1 - Finds the largest value less than or equal to the lookup_value. The range of cells should be sorted in ascending order
0 - Finds the first value equal to the lookup_value
-1 - Finds the smallest value that is larger than or equal to the lookup_value. The range of cells should be sorted in descending order

The example below demonstrates the MATCH function being used to find the position of James in the list.

How to use the MATCH function

Download the Excel Formulas Ebook for a fast and simple guide to the most useful Excel formulas.

Follow us on

Guides

Other Lookup & Reference Functions