use match from the bottom to the top.
 

the match function gives the index from the top
is it possible to use match from the bottom to the top?
ie. the meaning of "=MATCH(A1,$A$8:$A$1,0)"

use match from the bottom to the top.
 

What kind of result do you want?

MATCH returns a number that is the matched lookup_values *relative* position
in the lookup_array. You can use another formula that will "match" the *last
instance* of the lookup_value which means it is effectively working from
bottom to top.

--
Biff
Microsoft Excel MVP


"kang" wrote in message
...
the match function gives the index from the top
is it possible to use match from the bottom to the top?
ie. the meaning of "=MATCH(A1,$A$8:$A$1,0)"

use match from the bottom to the top.
 

what's the formula that gives the last one of the matched instance?

T. Valko wrote:
What kind of result do you want?

MATCH returns a number that is the matched lookup_values *relative* position
in the lookup_array. You can use another formula that will "match" the *last
instance* of the lookup_value which means it is effectively working from
bottom to top.

use match from the bottom to the top.
 

Assume:

A1:A10 is the range to search and you want the corresponding value from
B1:B10.

D1 = lookup_value

=IF(COUNTIF(A1:A10,D1),LOOKUP(2,1/(A1:A10=D1),B1:B10),"")

--
Biff
Microsoft Excel MVP


"kang" wrote in message
...
what's the formula that gives the last one of the matched instance?

T. Valko wrote:
What kind of result do you want?

MATCH returns a number that is the matched lookup_values *relative*
position
in the lookup_array. You can use another formula that will "match" the
*last
instance* of the lookup_value which means it is effectively working from
bottom to top.