How to Use VLookup for Bank Reconciliation - Excel (book balancing)
ฝัง
- เผยแพร่เมื่อ 7 ส.ค. 2024
- *NOTE*: When you're highlighting the table array...make sure the unique key is in the first column of data that you're highlighting or you'll get an error.
I wish you added the worksheets that you used in your vid to the description so we can download it to practice
That's really helpful, thank you!
Very helpful! Thank you.
Thank you for this. Quick and easy to understand explanation!
Very helpful. Thanks
Super helpful ! Thank you very much 👏🏽👏🏽
Only good for checks for wires and ACH you can do vlookup using the transaction date. This helps only if you are recording your wires and ACH transactions on the same day as they hit your account
This was helpful 🙂
Can you used vlookup in trade debtors reconciliation?
Thank you ❤
Thank You!
thank you so much
Just wonderful... 🙂🙂
Thanks for the comment! I really do hope it was helpful.
@@adventuresinTW but i guess there will be dr and cr amount in bank statement. How can we reconcile those data?
Roshan Chettri not sure if bank statements changed, but when I worked in the office years ago...the csv file from the bank statement had both debits and credit under one column.
Hi, Andrew if there is no currency sign so in that case what we need to put in key 3 Or 4 ?
Hi Firoz, if I understood your question correctly...currency sign should not matter. If there are no currency signs, it should not effect the formula since currency sign is just a format. Example: $0.01 or 0.01, both should still work. So you can keep using the same formula with or without the currency sign.
This ain’t to look for double entries though
Hi, what if there's no transaction id? Only the date, description, and amount. Would this still be possible?
Hey there, unfortunately Vlookup needs an ID or some kind of unique identifier. Since dates, descriptions, and amount could have duplicate info, vlookup won’t be able to distinguish the difference.
You can create unique id's by typing a few sequential numbers and then dragging all the way down; do the same on the other tab in the first column
@@Xy-oo2sm is reco possible that way??
Can i ask why need to key "3 " in vlookup formula?
Hello, I used column "3" because thats where the dollars amounts were. You would need to use the "column" that has the dollar amounts you're trying to match. For example, if my bank statement had the dollar amounts on column 4, then I would use column 4 in the vlookup formula. Hope this helps.
@@adventuresinTW thank you.👍
Helloo Sir What if you don't have Transaction ID or Reference?
Thank you
type in a few sequential numbers as your id, and then drag all the way to your last row; do the same thing on the other tab in the first column and drag all the way down
or sort your data first on both tabs and then add your id numbers in both tabs; making both tabs have the exact same id number with the vendor names
This was a bit helpful, next time please, kindly zoom in so we can clearly see the formulars, thank you.
I am just getting error in value
What went wrong?
I would need to see a screen shot of the data sets that are being used. Vlookup can be very sensitive. Can PM me. Just cover up any private data.
@@adventuresinTW how do I pm you?
@@sadikshyahumagain4399 Sorry, I thought TH-cam had a PM system. I'm guessing a "value error" is that the data is in the wrong order somewhere. Usually, its the "table-array" or "col-index".
@@adventuresinTW I tried everything it's not working.
@@sadikshyahumagain4399 do you use Discord? Maybe I try to help there.
What if you don't have a unique transaction number in the books or bank and want to merely go by the date?
A single date could have millions of transaction with the same dollar amount and you wouldn’t be able to determine which transaction is the one you’re looking for. Dates aren’t good identifiers for that reason.
@@adventuresinTW Thanks for the reply! I was afraid you were going to say that. The problem accountants face is that a client may not necessarily give each transaction a unique identifier/code. So you are left with bank statements in csv. format and the client's books in raw form.
Cool skating videos as well btw. That really takes me back a decade or so ago!
@@philipshergold4771 you are absolutely right about some csv not having identifiers. The company I used to work for had that issue and then management brought in a third party software company to automate reconciliations. Made the job easier but lots of people got laid off unfortunately.
Skating is long time hobby of mine. It keeps me young, lol.
How if you dont have the unique transaction number.How you match ?
@@casperelena77 create your own sequential id's( a few) and then drag down to your last row; repeat the same thing on the other tab - just make sure the names and id's on both tabs are the same
03:10 - no need to drag, just double click twice man.
This video is to much zoomed out for a youtube video.
A1 🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥
Super helpful ! Thank you very much 👏🏽👏🏽