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.