Doesn't work as the values of the cell ascend - I've even tried changing the
formula to descend ie. C32:C3 - but it automatically swops back round

"Duke Carey" wrote:

You want to use

=MATCH( value, range, -1)


From the Help file on Match(), regarding the 3rd argument:

Match_type is the number -1, 0, or 1. Match_type specifies how Microsoft
Excel matches lookup_value with values in lookup_array.

If match_type is 1, MATCH finds the largest value that is less than or equal
to lookup_value. Lookup_array must be placed in ascending order: ...-2, -1,
0, 1, 2, ..., A-Z, FALSE, TRUE.

If match_type is 0, MATCH finds the first value that is exactly equal to
lookup_value. Lookup_array can be in any order.

If match_type is -1, MATCH finds the smallest value that is greater than or
equal to lookup_value. Lookup_array must be placed in descending order: TRUE,
FALSE, Z-A, ...2, 1, 0, -1, -2, ..., and so on.