You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"simonc" wrote in message
...
Thank you for this. The LOOKUP method allows me to do what I want although
I
had to dig deep in the user group posts to see how it works.
Congratulations
to whoever dreamed up that highly unlikely syntax.

"T. Valko" wrote:

What result are you wanting?

Do you want the value of the left column that corresponds to the *last*
instance of x,y ?

If so, there are a couple of ways to do this.

It appears that the values in the left column are in ascending order for
each x,y. So, you can use a formula to find the MAX of the left column IF
the right column = x,y

Try this array formula** :

=MAX(IF(B2:B14="400,50",A2:A14))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

Another way, and this doesn't need to have the left column in ascending
order. This one actually looks for the *last* instance of x,y :

Normally entered:

=LOOKUP(2,1/(B2:B14="400,50"),A2:A14)

--
Biff
Microsoft Excel MVP


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