Results 1 to 3 of 3

Thread: Look up left / right cell to get maximum value

  1. #1
    Acolyte thousand's Avatar
    Join Date
    Apr 2017
    Posts
    30
    Articles
    0
    Excel Version
    2010

    Look up left / right cell to get maximum value



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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

  2. #2
    Acolyte Roger Haney's Avatar
    Join Date
    Jan 2021
    Posts
    23
    Articles
    0
    Excel Version
    Microsoft Excel Office 365
    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]

  3. #3
    Acolyte caabyyc's Avatar
    Join Date
    Dec 2014
    Location
    Burnaby/Calgary
    Posts
    43
    Articles
    0
    Excel Version
    2016
    B5=if(b2<>"",b2,if(count(a2:c2)=2,max(a2:c2),""))
    Click image for larger version. 

Name:	Capture.JPG 
Views:	4 
Size:	40.2 KB 
ID:	10822

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •