How to find last match in a column?

[curiousgeorge408]

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

 

[Dave Peterson]

=LOOKUP(2,1/(C1:C99=MAX(C1:C99)),A1:A11)

Adjust the range to match, but don't use the whole column (unless you're using
xl2007).

 

[Ragdyer]

Dave also meant to say that the size of the Column A range should match the
size of the Column C range.

 

[Dave Peterson]

That was a typo on my part.

Thanks for catching it, RD.

 

[curiousgeorge408]

=LOOKUP(2,1/(C1:C99=MAX(C1:C99)),A1:A11)

Cute! Thanks.

Adjust the range to match, but don't use the whole column
(unless you're using xl2007).

By "whole column", I presume you mean a range ending with row 65536.
If not, please elaborate.

 

[Dave Peterson]

I mean this won't work in xl2003 and before:

=LOOKUP(2,1/(C:C=MAX(C:C)),A:A)

But this will work:

=LOOKUP(2,1/(C2:C65536=MAX(C2:C65536)),A2:A65536)