Hi, I'm trying to calculate how far from the bottom (last entry) of a table
other last column entries are.
LOOKUP is locating the last value in any given column for me, and by then
applying MATCH, which generates a ROW number, I take this away from the last
row number of the table (remotely referenced in A2) to give me the distance
from the bottom e.g. 12 rows;

=SUM($A$2-MATCH(LOOKUP(100,CT:CT),CT:CT,0))

However if the last value is duplicated in the same column, MATCH is giving
me the row number for that entry rather than the final entry.
How can I force it to give me the row number for the final entry for that
value ?
Or is there a better way to achieve what I'm doing ?
Any advice appreciated!

Try this:

=$A$2-LOOKUP(100,CT:CT,ROW(CT:CT))


--
Biff
Microsoft Excel MVP


"Graham" wrote in message
...
Hi, I'm trying to calculate how far from the bottom (last entry) of a
table
other last column entries are.
LOOKUP is locating the last value in any given column for me, and by then
applying MATCH, which generates a ROW number, I take this away from the
last
row number of the table (remotely referenced in A2) to give me the
distance
from the bottom e.g. 12 rows;

=SUM($A$2-MATCH(LOOKUP(100,CT:CT),CT:CT,0))

However if the last value is duplicated in the same column, MATCH is
giving
me the row number for that entry rather than the final entry.
How can I force it to give me the row number for the final entry for that
value ?
Or is there a better way to achieve what I'm doing ?
Any advice appreciated!

Once again you've been a great help, Many thanks

"T. Valko" wrote:

Try this:

=$A$2-LOOKUP(100,CT:CT,ROW(CT:CT))


--
Biff
Microsoft Excel MVP


"Graham" wrote in message
...
Hi, I'm trying to calculate how far from the bottom (last entry) of a
table
other last column entries are.
LOOKUP is locating the last value in any given column for me, and by then
applying MATCH, which generates a ROW number, I take this away from the
last
row number of the table (remotely referenced in A2) to give me the
distance
from the bottom e.g. 12 rows;

=SUM($A$2-MATCH(LOOKUP(100,CT:CT),CT:CT,0))

However if the last value is duplicated in the same column, MATCH is
giving
me the row number for that entry rather than the final entry.
How can I force it to give me the row number for the final entry for that
value ?
Or is there a better way to achieve what I'm doing ?
Any advice appreciated!

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Graham" wrote in message
...
Once again you've been a great help, Many thanks

"T. Valko" wrote:

Try this:

=$A$2-LOOKUP(100,CT:CT,ROW(CT:CT))


--
Biff
Microsoft Excel MVP


"Graham" wrote in message
...
Hi, I'm trying to calculate how far from the bottom (last entry) of a
table
other last column entries are.
LOOKUP is locating the last value in any given column for me, and by
then
applying MATCH, which generates a ROW number, I take this away from the
last
row number of the table (remotely referenced in A2) to give me the
distance
from the bottom e.g. 12 rows;

=SUM($A$2-MATCH(LOOKUP(100,CT:CT),CT:CT,0))

However if the last value is duplicated in the same column, MATCH is
giving
me the row number for that entry rather than the final entry.
How can I force it to give me the row number for the final entry for
that
value ?
Or is there a better way to achieve what I'm doing ?
Any advice appreciated!