Bank Reconciliation Formula VLOOKUP Excel (Fastest Guide)

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:

Bank Debit Data

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.

Jason John Wethe
Follow Me
Latest posts by Jason John Wethe (see all)
Scroll to Top