Excel-lent? I dunno

Having spent much of the last goodness-knows-how-long wrestling with the intricacies of Ruby On Rails On Linux On Oracle (a challenge for a Windows-constrained one such as I) it was a pleasant diversion to be presented by a colleague with an Excel question that I couldn’t immediately answer.

He had some data of this form:

…and he wanted the value from column B from the row containing the last of a particular value in column A. So for “A”, he wanted 68, “B” would give 95 and “C” 12.

I came up with this (for “C”):

{=INDEX(B1:B6,MAX(IF(A1:A6="C",ROW(A1:A6),0)))}

It gets a bit more complicated if the data doesn’t start on row 1, but you should see what’s going on.

I can’t say I like it very much, for all it does what was requested.