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