How can I find the __last__ match in a column? And how do I get the
value of another cell in the same row?

Suppose C1:C6500 contains "random" values, some of which might be
duplicates. (By "random", I mean they are not sorted.) I want to
find the __last__ occurence of the largest value. And I want to get
the value of the corresponding cell in column A.

I believe the following should find the __first__ occurence and
returns the corresponding column-A value:

=index(A1:A6500, match(max(C1:C6500), C1:C6500, 0), 1)

I don't see any way to extend the concept so that I find the __last__
match; and I have not found alternate Excel functions to use.

If VBA is the only solution, I would appreciate it if someone would
offer a good implementation. My VBA skills are poor. Below is my
first stab at it, just for grins.

PS: Forgive me if this question has been asked and answered
recently. I think it has. But I think Google Groups search is
screwed up today. It coughs up "matches" that do not match my search
criteria. I cannot see the forest for the trees.


Function matchlast(v As Variant, r As Range) As Long
Dim i As Long
' we should select from last to first cells in range;
' that would avoid searching the entire range every
' time. but I do not remember how to do that.
matchlast = 0
i = 0
For Each cell In r
i = i + 1
If cell = v Then matchlast = i
Next
End Function