How to Perform Bank Reconciliation Using VLOOKUP in Excel

Accountants always have to do reconciliations in their daily job. It helps to observe and maintain equality in both-sided transaction ledgers. One of the most common reconciliations is bank reconciliation. Most of the time, the records are in thousands. With the help of the Excel VLOOKUP function, you can complete the task easily. In this tutorial, I will write how to perform bank reconciliation with the VLOOKUP function in Excel.

Overview of bank reconciliation with the VLOOKUP function

The above image is an overview of bank reconciliation with the VLOOKUP function. From this image, we can easily find out which transaction row has discrepancies in values.

How to Perform Bank Reconciliation by Applying VLOOKUP in Excel: 3 Easy Steps

I will create two datasets of a Bank named Bank Statement and General Ledger, where the attributes are Transaction ID, Clients, Date, and Amount. From the datasets, I will show the process of reconciliation of the Amount by using the VLOOKUP function. I used the Microsoft Excel 365 version in this tutorial.

Step-1: Creation of Basic Dataset

The first dataset is Bank Statement. This Dataset holds the transaction records from the bank. The Transaction ID is the unique identification number of each transaction, the Clients is the name of the organization that had the transaction, the Date column has the date information, and lastly, the Amount column holds the transaction amount.

Bank Statement Dataset

The second dataset is General Ledger. This is the record of the accountant. Similar to the Bank Statement dataset, this dataset also has identical columns.

General Ledger Dataset

I will match the General Ledger dataset with the Bank Statement dataset to reconcile the amount.

Read More: Automation of Bank Reconciliation with Excel Macros

Step-2: Calculation of Differences by Using the VLOOKUP Function

To do the comparison between the Amount column of both the Bank Statement and the General Ledger, I will first create a column in the General Ledger dataset.

Creating a Column in General Ledger

Now, I will compare the Amount columns. To do that, I will find exact matches with the VLOOKUP function, and then subtract the Amount of General Ledger from the Amount of the Bank Statement.

  • Select cell F5.
  • Type the following formula to make the comparison:

=VLOOKUP(B5,'Bank Statement'!B5:E15,4, FALSE)-E5

Formula Breakdown

  • VLOOKUP(B5,’Bank Statement’!B5:E15,4, FALSE)The VLOOKUP function will search for the exact match between the Transaction ID of both datasets and return the value of Amount.
    • B5 is the lookup value, which is the Transaction ID of the General Ledger.
    • ‘Bank Statement’!B5:E15 is the lookup array, which is the whole dataset of the Bank Statement.
    • 4 is the column number of Amount to which the value will be returned.
    • FALSE is the condition for exact match.
      • Output$26,800.00.
  • VLOOKUP(B5,’Bank Statement’!B5:E15,4,FALSE)-E5 → becomes
    • $26,800.00-E5 → Here, the Amount of both datasets will be subtracted.
      • Output$0.00.

Making Comparison Using VLOOKUP function

  • After that, fill the other cells using the Autofill Feature.

Auto-filling the formula

Now, I have both dataset amounts comparison.

Read More: How to Reconcile Two Sets of Data in Excel

Step-3: Applying Filter Feature to Highlight Transactions With Discrepancies

When the datasets are larger, it is difficult to find out the discrepancy or reconcile them. I will use the Filter feature of excel to filter out the cells with values of $0.00.

  • Select the whole dataset.
  • Find the Filter option from Sort & Filter under the Editing group.

Using the filter option to find out the discrepancies

  • Click on Filter.

Unchecking the cells with zero value

  • Now, unselect the option with $0.00.
  • This way, I have the dataset of General Ledger with only the Amount of discrepancy cells.

The final look of the dataset is like the following image below.

The final result of the bank reconciliation using the VLOOKUP function process

So, the faulty calculated transactions are visible now. You have to just deduct the extra $100.00 and $400.00 from the faulty values now. Then all of these miscalculations will be reconciled.

Read More: How to Reconcile Data in Excel

Practice Section

On the Practice Section worksheet, you will find a dataset to practice on your own. Make the comparison between the Bank Statement dataset and the Practice Section dataset.

Practice section of bank reconciliation using VLOOKUP function

Download Practice Workbook

You can download the practice workbook here.


In this article, you can learn how to do bank reconciliation with the VLOOKUP function in Excel. You can suggest your thoughts about this article in the comment section below.

Related Articles

<< Go Back to Excel Reconciliation Formula | Excel for Accounting | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Rabeya Islam
Rabeya Islam

Rabeya Islam, BSc, Computer Science and Engineering, East West University, Bangladesh, has been working with the SOFTEKO for more than one and half years. She has written some articles for ExcelDemy. Currently, she is working as the team leader, oversees the day-to-day work, and leads the SQA team Excel Extensions project. She has built the workflow and the structure of the extension testing for the team. Her work and learning interests vary from Microsoft Office Suites, VBA, and... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF