Hi,
I have attached an example sheet. Basically I have an ordered list (dates in column A, people's names in column B, scores in column C). In column D I want to find the last score achieved by the person in that row (e.g. Tom in row 6) and I have achieved that using the following formula:
Formula:Please Login or Register to view this content.
You can see it correctly returns 3.5. But what I'd then like to do is to find the second last instance of the name (i.e. two back) so in this case it should be 2. Is there any way to do this with modification of the above formula? Ideally if possible I'd like to avoid array formulas or overly complex formulas as I'll need to run this on a sheet with around 100,000 lines so want the most clean and efficient option for calculation times.
Thanks in advance!
Bookmarks