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.

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.

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.

You can have a second list which is the same as the first one, but upside down.
If your data is in A1:B100, put this in C1:

=INDEX($B$1:$B$100,100-ROW()+1)

Copy down to C100

In D1:

=INDEX($A$1:$A$100,100-ROW()+1)

and copy down too.

Now, with your value to search in E1, you can find the corresponding item:

=VLOOKUP(E1,C1:D100,2,0)

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


"simonc" wrote in message ...
|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.
|
|

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.

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.

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.