Look up left / right cell to get maximum value

I am trying to lookup a year and get the max value (either in the previous year or the following year) if there is a missing value (e.g. 2007).


Here is the table I have -

Here is the table I want -


Any suggestions? Thanks

Assuming the word "Country" is in cell A1, then this formula placed in cell B3 and filled across will give you what you need : IF(AND(ISNUMBER(A2),ISNUMBER(C2),ISBLANK(B2)),MAX(A2,C2),""). You can then copy the results and "Paste special" the values into the row above.


Here is the table I have -

Here is the table I want -


Any suggestions? Thanks [/QUOTE]

B5=if(b2<>"",b2,if(count(a2:c2)=2,max(a2:c2),""))