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

This is an overview.

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.

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

Match the General Ledger dataset with the Bank Statement dataset to reconcile the amount:

Step 2 – Calculate Differences Using the VLOOKUP Function

Insert a column in the General Ledger dataset.

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.

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

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.

• Click Filter.

• Uncheck \$0.00.

General Ledger displays the Amount of discrepancies only.

This is the output.

Read More: How to Reconcile Data in Excel

Practice here.

Related Articles

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

Get FREE Advanced Excel Exercises with Solutions!
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

Advanced Excel Exercises with Solutions PDF