On Thu, 7 Sep 2006 07:07:01 -0700, Nav wrote:

Hello

I am trying to find out if there is a way or work around to use vlookup in a
list where it finds the last occurance of an item and gives the corresponding
value in relation to it.
Eg.

Pear 5
Apple 5
Orange 3
Plum 2
Apple 1

I need it to look at the last occurance of Apple and return the value 1, but
the problem is the vlookup finds the first occurance only and returns 5.

Any ideas anyone.

Thank you in advance for any help.

Regards,
Nav

You could use this **array** (entered with <ctrl<shift<enter ) formula:

=INDEX(B1:B10,MAX(ROW(A1:A10)*(A1:A10="Apple")))

With your fruit in A1:A10 and your values in B1:B5.


--ron