Look up left / right cell to get maximum value

[thousand]

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 -

Country	2001	2002	2003	2004	2005	2006	2007	2008
A					123	100		140

Here is the table I want -

Country	2001	2002	2003	2004	2005	2006	2007	2008
A					123	100	140	140

Any suggestions? Thanks

[Roger Haney]

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 -

Country	2001	2002	2003	2004	2005	2006	2007	2008
A					123	100		140

Here is the table I want -

Country	2001	2002	2003	2004	2005	2006	2007	2008
A					123	100	140	140

Any suggestions? Thanks [/QUOTE]

[caabyyc]

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