+ Reply to Thread
Results 1 to 6 of 6

Easy bank reconciliation

  1. #1
    Registered User
    Join Date
    05-03-2011
    Location
    Dubai
    MS-Off Ver
    Excel 2003
    Posts
    2

    Easy bank reconciliation

    How to make reconciliation using VLOOKUP comparing two sheet, debit and credit column

  2. #2
    Registered User
    Join Date
    08-17-2012
    Location
    Lisbon, Portugal
    MS-Off Ver
    Excel 2003 & 2010
    Posts
    82

    Re: Easy bank reconciliation

    Hello,

    Dependending on the information you have in both sheets.
    What information do you have in sheet1 and sheet2?

    If in one of the two sheets you mentioned you have the same data, you can start there.

    Example:

    Assumptions:
    sheet A - bank movements
    sheet B - booked/accounted movements

    If in both sheets there is a data like bank ref you may use vlookup. That way you could search for a specific bank ref and link the open bank movement to the booked movement. One less open item

    However if in one of the sheets you have several bank ref's you will probably need a sumif instead of vlookup. Remeber that vlookup only returns you the first lookup_value that he finds.


    Rgds,
    FCarv

  3. #3
    Registered User
    Join Date
    05-03-2011
    Location
    Dubai
    MS-Off Ver
    Excel 2003
    Posts
    2

    Re: Easy bank reconciliation

    Dear Sir,

    I have uploaded two sheets namely GL Account and Bank Statement and the formula GL account. But formul having some error.

    Kindlyy check and correct

    Thanks and Regards,

    ABOOBACKER V.P
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    08-17-2012
    Location
    Lisbon, Portugal
    MS-Off Ver
    Excel 2003 & 2010
    Posts
    82

    Re: Easy bank reconciliation

    Hello aboobacker,

    Unfortunately you can't use a vlookup there to help you due to the fact that you don't have the same data on both sheets, therefore you can't look it up.

    If you can manage to put a ref in your GL Account that match your Bank Statement (for example for the amount of 85500 the ref "009255" that corresponds to the number of the cash payment) then you will be able to retrieve the information you want based on that data.

    But for that you need to book those amounts in your GL account with those ref's - it's something that gives more work while booking but then it will be easier when you are doing the bank reconciliation.

  5. #5
    Registered User
    Join Date
    10-02-2018
    Location
    West Palm Beach
    MS-Off Ver
    360
    Posts
    11

    Re: Easy bank reconciliation

    I am using this formula to look at 2 columns of data and match them
    =IF(ISNA(MATCH(U2239,$U$1:U2239,0)),IF(ISNUMBER(MATCH(-U2240,$U$2:$U$2259,0)),"net",""),"")

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,365

    Re: Easy bank reconciliation

    Stick to your own thread, please - you are not to hijack other threads. Thanks!

    Replies to Cheshire Cat should go here: https://www.excelforum.com/excel-for...iliations.html
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1