Easy bank reconciliation

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

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

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

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.

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",""),"")