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:
    223
  2. Wayne

    Conversion Excel 97 to Excel 2003

    Wayne, Sep 8, 2004, in forum: Microsoft Excel New Users
    Replies:
    2
    Views:
    270
    Gord Dibben
    Sep 8, 2004
  3. Guest

    V lookup in Excel 2003

    Guest, Nov 6, 2004, in forum: Microsoft Excel New Users
    Replies:
    1
    Views:
    225
    Ken Wright
    Nov 6, 2004
  4. Lady Layla

    How do I convert Excel 2003 to Excel 98 format?

    Lady Layla, Mar 28, 2005, in forum: Microsoft Excel New Users
    Replies:
    8
    Views:
    362
    Gord Dibben
    Mar 31, 2005
  5. Guest

    Lookup "greater than or equal to" in lookup array

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

    Excel 2000 and Excel 2003 in Office 2003

    Guest, Dec 21, 2006, in forum: Microsoft Excel New Users
    Replies:
    0
    Views:
    251
    Guest
    Dec 21, 2006
  7. Scott Sornberger

    How do I get 2003 Macros and Toolbar Buttons form Excel 2003 to 20

    Scott Sornberger, Mar 18, 2008, in forum: Microsoft Excel New Users
    Replies:
    2
    Views:
    325
    Scott Sornberger
    Mar 19, 2008
  8. DB.

    Excel 2003 to Excel 2007 problem.

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