VLOOKUP for latest non-empty cell

Question

Basically, I've got the following spreadsheet.

There are dates along the top. I do a certain amount of past papers each week, and record the highest score I get in a week in cell (paper, week). The column marked Latest Score has the following formula:

=VLOOKUP(B17, B17:U17, 12, FALSE)

This checks the column of one week. However, as you can see, there are zeros where there isn't a paper for that week. I'd like it so that that VLOOKUP can find the most recent column in which there is a score for that row, and return that value in the Latest Score column.

Answer 1

This answer on StackOverflow should solve your problem.

To quote:

For numbers get last value with this formula in Z2

    =LOOKUP(9.99E+307,A2:Y2)

or for text...

    =LOOKUP("zzz",A2:Y2)

or for either...

    =LOOKUP(2,1/(A2:Y2<>""),A2:Y2)

all the formulas work whether you have blanks in the data or not......