MATCH function to return relative position of a value

You can return the relative position of a value in a range/array by using the MATCH function.

The example below works on a vertical range/array and returns the position of the exact match to the cell/value referenced.

The formula is built as follows – ‘=MATCH(Value to match, Range to look in, match type)

The match types available are


The same can used in a horizontal range/array


In order to use the function to find the nearest to value you can utilise the less than (1) or greater than (-1) match types.

To use these match types the range/array to be checked needs to be in ascending alphabetical/numeric order to use the less than match type and descending alphabetical/numeric order to use the greater than match type.

If the range/array is not in the required order when using the 1 or -1 match types, then then function will return en error (#N/A).


Get more great content from Rousseau Associates

Start your project with us today

Call +44 (0)1757 269461

Discuss your requirements today