Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    Dec 2009
    Location
    warren, ohio
    Posts
    66
    Thanks
    1
    Thanked 13 Times in 6 Posts

    need help with vlookup

    We are using excel 2003. (2007 is available but I don't want to fight the ribbon). I am trying to compare data in one tab to data in another. It is a basic bank check reconciliation.

    I always struggle when setting up a vlookup.

    We download a csv from the bank in one tab and put our general ledger data in a second tab.

    I want to use vlookup from our ledger tab to find the check number in the bank tab(called january pnc). I need to know what does not match by check number column, as well as make sure the amounts match in another column.

    In my ledger tab,I use this formula =VLOOKUP(G6,'january pnc'!E1:E209,2,0). The formula is in column h and column I is blank to display the results. The data is sorted by date in both tabs, not by check number the vlookup is using.

    G contains our check number in our ledger tab, E contains the check number in the bank tab.

    I get #ref error on my formula. Naturally, I made sure this first example had a matching check number in both tabs.I also tried sorting by check number in the january pnc tab, but I still got #ref.

    After I get this working, I will need additional help on doing the match on a substring of the check number. But first I need to get the formula working.

    google showed me I needed to use quotes around my tab name, but my formula looks ok to me after that. Where am I going wrong?

    Thanks in advance.

  2. #2
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    Your formula: =VLOOKUP(G6,'january pnc'!E1:E209,2,0) is trying to return the SECOND column of the referenced data, but you only have one "column" of data, E1:E209. VLOOKUP is trying to find G6 from the E range. But, you need a second column in that reference to be the result.

    Maybe it's: =VLOOKUP(G6,'january pnc'!E1:F209,2,0) if the result you want back is in the F column.
    So, after you match the check number in G6 with the E column, this would return the corresponding value in the F column (col. 2).

    If you're just trying to match the check number, use MATCH rather than VLOOKUP.

  3. #3
    Star Lounger
    Join Date
    Dec 2009
    Location
    warren, ohio
    Posts
    66
    Thanks
    1
    Thanked 13 Times in 6 Posts
    If I change the formula to : =VLOOKUP(G6,'january pnc'!E1:F209,1,0) it returns the check number from the january pnc sheet, column E. My original formula I would have expected to return the value of column F, which was a substring of the check number. I get #ref. I changed to =VLOOKUP(G6,'january pnc'!E1:F209,3,0) expected the check amount in column G. I get #ref.

    I will try MATCH and see how that works.

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    =VLOOKUP(G6,'january pnc'!E1:F209,2,0)
    as suggested
    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    As Rory pointed out, you need a "2"... in your formula: =VLOOKUP(G6,'january pnc'!E1:F209,3,0) there are only TWO columns (E and F) and you're trying to return something from column "3" (there isn't a column 3).

Posting Permissions

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