Lookup problem in Excel 2003

Discussion in 'Microsoft Excel New Users' started by abentleyg38@gmail.com, Apr 4, 2012.

  1. Guest

    I had a formula in an Excel workbook called Payments to update the bank balance during 2011-2012:

    =LOOKUP(1E+100,'\\Pfadc01\company data\ADMINISTRATION\Excel\Accounts 2011-2012\Bank 11-12\[2011-2012 Bank balance.xls]PF'!$F:$F)

    This worked perfectly well until I needed a similar formula for 2012-2013. I have amended it as appropriate but it only returns 0.00 on my Payments sheet.

    This is the formula I am using:
    =LOOKUP(1E+100,'\\Pfadc01\company data\ADMINISTRATION\Excel\Accounts 2012-2013\Bank 12-13\[2012-2013 Bank balance.xls]PF'!$F:$F)

    Can anyone see where I have gone wrong please? Help would be greatly appreciated!
     
    , Apr 4, 2012
    #1
    1. Advertisements

  2. joeu2004 Guest

    <> wrote:
    > I had a formula in an Excel workbook called Payments
    > to update the bank balance during 2011-2012:
    > =LOOKUP(1E+100,'\\Pfadc01\company data\ADMINISTRATION\
    > Excel\Accounts 2011-2012\
    > Bank 11-12\[2011-2012 Bank balance.xls]PF'!$F:$F)
    >
    > This worked perfectly well until I needed a similar
    > formula for 2012-2013. I have amended it as appropriate
    > but it only returns 0.00 on my Payments sheet.
    > This is the formula I am using:
    > =LOOKUP(1E+100,'\\Pfadc01\company data\ADMINISTRATION\
    > Excel\Accounts 2012-2013\
    > Bank 12-13\[2012-2013 Bank balance.xls]PF'!$F:$F)
    >
    > Can anyone see where I have gone wrong please?


    Quite possibly nowhere.

    The formula returns the last numeric value in column F. Presumably, 0.00 is
    the last numeric value.

    If you want the last non-zero numeric value, you can use the following
    normal-entered formula (just press Enter as usual) if the last non-empty
    cell contains a numeric value, not text.

    =LOOKUP(1E+100,1/('\\Pfadc01\company data\ADMINISTRATION\
    Excel\Accounts 2012-2013\
    Bank 12-13\[2012-2013 Bank balance.xls]PF'!$F:$F<>0),
    '\\Pfadc01\company data\ADMINISTRATION\
    Excel\Accounts 2012-2013\
    Bank 12-13\[2012-2013 Bank balance.xls]PF'!$F:$F)
     
    joeu2004, Apr 5, 2012
    #2
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. John Doe

    Most likely Lookup -formula problem

    John Doe, May 18, 2004, in forum: Microsoft Excel New Users
    Replies:
    4
    Views:
    189
  2. Guest

    V lookup in Excel 2003

    Guest, Nov 6, 2004, in forum: Microsoft Excel New Users
    Replies:
    1
    Views:
    197
    Ken Wright
    Nov 6, 2004
  3. Guest

    Lookup "greater than or equal to" in lookup array

    Guest, Feb 15, 2006, in forum: Microsoft Excel New Users
    Replies:
    3
    Views:
    272
    Guest
    Feb 16, 2006
  4. Guest

    Excel 2000 and Excel 2003 in Office 2003

    Guest, Dec 21, 2006, in forum: Microsoft Excel New Users
    Replies:
    0
    Views:
    201
    Guest
    Dec 21, 2006
  5. DB.

    Excel 2003 to Excel 2007 problem.

    DB., Aug 9, 2009, in forum: Microsoft Excel New Users
    Replies:
    0
    Views:
    205
Loading...

Share This Page