This post will teach you VLOOKUP for bank reconciliation.
The VLOOKUP function is also a useful tool for matching cleared checks, identifying outstanding checks, and detecting unauthorized check issuances.
You use the function for automated bank reconciliation excel.
For more features, you can also use the tool for reconciling check numbers.
Also, It shows #N/A when it can’t match.
So, it can find outstanding checks too.
By reconciling from the bank to the depositor’s records, VLOOKUP also detects unrecorded check disbursements.
Let’s explore the VLOOKUP in relation to bank reconciliation.
What are bank reconciliation formulas?
Click here to learn more about bank reconciliation formulas.
Here is how to use VLOOKUP to find cleared checks
The formula takes a value from bank debit data based on the lookup value in Column C (check no.1).
In this case, VLOOKUP found check no. 1 in column C of the Bank Debit sheet and returned the amount in Column D amounting to $200,000.00.
Since both records have no difference, there are no errors in the recording.
See the example bank data below:
Finding Outstanding Checks
VLOOKUP shows the error code #N/A, when the lookup value does not exist in the search table.
So, it means that the check number is still outstanding.
By sorting the #N/A, you get the lists of all outstanding checks. It helps you reconcile checks faster.
When you reconcile starting from the bank, some #N/A could appear in the working paper.
You’ll see one #N/A in the example, an example of unrecorded check.
I hope you like our tutorial today. Please share the post if you like it.
- What is a Bank Reconciliation Accountant? - November 26, 2023
- 7 Steps to Make a Bank Reconciliation - November 26, 2023
- Analyzing Accounts Receivable in Excel (with example) - November 25, 2023