I understood MATCH with option 1 will only work if the lookup array is sorted
in ascending order, which is not the case here.

"Don Guillett" wrote:

Did you look in the help index for MATCH?
=MATCH(2,F:F,0)
=MATCH(2,F:F,1) or leave out the ,1 as it is the default

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"simonc" wrote in message
...
Here is a little sample from a very large worksheet. The first column
contains values which I want to use in a calculation, and the second
column
index values representing coordinates of a location.

3435.3 350,50
3999 350,50
1884.7 400,50
1942.4 400,50
2156.7 400,50
2259.7 400,50
2403.9 400,50
3601.6 400,50
3710.3 400,50
3999 400,50
2004.8 100,200
2031.1 100,200
2057.4 100,200

I can easily use MATCH to find the first row which has the index 400,50,
but
how can I use it to determine the last row which has this value? The
values
are not sorted in any order so it wouldn't be possible to predict the
value
of the index following 400.50. Is there a MATCH which will search up from
the
bottom of the worksheet? How could I incorporate some test to check
whether
the value of this index is the same or different on the following row?

Grateful for advice.