Quickly reconcile large number of checks using VLookup in Excel bank reconciliation

[@eileenmckeough4044]

This video is very helpful and exactly what I was hoping to find. Thank you. Your explanation of VLookups is very clear and more understandable than many others. Thank you.

[@switchexcel3244]

kzbin.info/www/bejne/jaeplquEjJyMfJY

[@yi8261]

Thank you! This video is very helpful and practical! This is something I was looking for. Hopefully, you can post more videos like this one.

[@mihaelalopez9284]

Thank you! Can't wait to use this for the bank recs going forward!

[@katorogojeta6496]

Did this work out for you?

[@slvrearview]

Please do not request access to be an editor of the file I've shared. You can simply download a copy of the worksheet without needing access to edit it. Do this by going to "File" and then "Download" and then choose MS Excel. Thanks!

[@GreedisGood83]

Hi, could this also be used to reconcile CC transactions, and ACH transactions that hit the bank, and hit the G/L?

[@saeedalam4386]

How we use vlookup or Match function If data entered in worksheet without a unique reference or blank reference against which we match our record I need your help kindly reply me.

[@slvrearview]

You can use vlookup function without unique references; however, it will only return the first data item that matches the search.

[@GreedisGood83]

Hi, also I'm unable to access the workbook. It's saying the link is invalid...

[@slvrearview]

Justin, sorry you're having an issue with the link. I just tried it on a couple different browsers without being signed in and it appears to be working for me. It's a Google doc link which can be downloaded. If you'd like I can email the attachment directly to you. And yes, you should be able to reconcile ACH and CCard transactions. However, my merchant processes these in batches, which makes it more difficult to reconcile individual transactions (more like daily batches). But you can use vlookup to match anything where you have a unique identifier. It might be easier to match by dollar amount. Just make sure that the table you are looking up from (the "checks cleared bank" tab) has the amount in ascending order, otherwise the vlookup won't function properly.

[@GreedisGood83]

Hey Man, thanks for the quick response. For the record I love the video, and you're right I tried downloading the workbook again, but this time on my home computer (not work PC) and it worked. My apologies. Thanks for answering my question in such detail. Great feedback, and its good to know I can use this video's teachings to also reconcile CC transactions, and ACH transactions. That's huge for me, and I agree I should be able to do it as long as the transactions have a unique identifier. My companies CC processor does use batched transactions daily per say. However, the machines are batched out after each transaction, or at least there supposed to be. Sometimes I get a group of 4 transactions on the GL side, that match 1 transaction in the bank. I see a lot of individual transactions line by line. So, yes it's easy enough to match those by dollar amount, but I also like to try and match by at least one, or two more criteria. One being DATE, the other being LOCATION of the machine. (For all transactions as each location has a unique ID # that should be shown with the deposit in the bank if procedures are properly followed. Anyways, the challenge I'm faced with is currently I do daily matching of my bank to my GL, but I manage only two affiliates within my company. There are 12 other accountants that manage 2 affiliates each, and my company needs a universal template that all accountants can use. Hence the reason why I viewed your video, and I'd like to try to use your template/file as a starting point to creating this "universal template." Do you mi? If you don't I'd really love to send you my progress as I develop this, and get your professional feedback? Let me know if that would be ok with you, and thanks again for everything.

[@switchexcel3244]

kzbin.info/www/bejne/jaeplquEjJyMfJY

[@zazalila5450]

hiii, how about error in bank recon?

[@slvrearview]

Can you explain your question a little more? I'm not sure what you're asking.

[@mdtauhidulislam6601]

my dear brother i need more update video regarding the same subject Thank you very much