How to Perform Bank Reconciliation Using the VLOOKUP Function in Excel – 3 Steps

This is an overview.

Overview of bank reconciliation with the VLOOKUP function

From this image, you can find transaction rows with discrepancies.


Step 1 – Create a Dataset

The first dataset is a Bank Statement: Transaction ID is the unique identification number of each transaction, Clients shows the name of the organization, Date has date information, and Amount holds the transaction amount.

Bank Statement Dataset

The second dataset is General Ledger. It is an accountant record.

General Ledger Dataset

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 – Calculate Differences Using the VLOOKUP Function

Insert a column in the General Ledger dataset.

Creating a Column in General Ledger

Compare the Amount columns:

Find exact matches with the VLOOKUP function, and subtract the Amount of General Ledger from the Amount of the Bank Statement.

  • Select F5.
  • Enter the following formula:

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

Formula Breakdown

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

Making Comparison Using VLOOKUP function

  • Drag down the Fill Handle to see the result in the rest of the cells.

Auto-filling the formula

 

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


Step 3 – Applying the Filter Feature to Highlight Transactions With Discrepancies

Filter cells with values of $0.00.

  • Select the whole dataset.
  • Select Filter in Sort & Filter in the Editing group.

Using the filter option to find out the discrepancies

  • Click Filter.

Unchecking the cells with zero value

  • Uncheck $0.00.

General Ledger displays the Amount of discrepancies only.

This is the output.

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

 

Read More: How to Reconcile Data in Excel


Practice Section

Practice here.

Practice section of bank reconciliation using VLOOKUP function


Download Practice Workbook

Download the practice workbook.


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

 

 

ExcelDemy
Logo