Excel Reconciliation Formula (for Data, Vendor, Bank)

In this article, we will learn about the Excel reconciliation formula. To reconcile data, we will use the VLOOKUP, INDEX, MATCH and SUMIF functions.

We do a lot of data sharing every now and then, and we need to avoid mistakes in the different versions of data. Data reconciliation plays an essential role in this case, and it finds the data variances that we can use to correct our data if needed.

Also, in banking, data reconciliation is a regular practice. We need to know how to apply different Excel functions to reconcile data properly.

Download Practice Workbook

You can download the workbook from here and practice yourself.

Use of Reconciliation Formula.xlsx

What Is Reconciliation?

The word reconciliation means the restoration of an entity. And data reconciliation refers to the process of data verification.

We find out dissimilarities between multiple sets of data by performing data reconciliation. This verification can be completed in regular time intervals or during sharing of data.

Reconciliation Formula in Excel: 4 Formulas

Our dataset has two segments. The left segment (B4:C12) is from the central database, whereas the right (E5:F12) is from the current worksheet. We need to reconcile these two datasets.

Method-01: Using VLOOKUP Function to Reconcile

Type the following formula in cell H5 and hit ENTER.

=VLOOKUP(E5,$B$5:$C$12,2,FALSE)

As a result, we will get the sales value of Coca-Cola from the central dataset (B4:C12).

For all other products, we can use the AutoFill feature.
Select cell H5, and place the cursor around the bottom right corner of cell H5.
As the AutoFill icon shows up, click and drag it up to cell H12.

Hence, we will get all the products’ sales values.

Now, we need to calculate the difference between the sales values in the central and current databases.
Type the following formula in cell I5 and press ENTER.

=F5-H5

As a result, we will get the difference for the product Coca-Cola.
We can see a ‘–’ symbol as the difference is zero (0).

Use the AutoFill feature to get the difference for all other products.

From the reconciliation result, we can say there are variances for the Mountain Dew and Miller Lite products.

Method-02: Applying INDEX and MATCH Functions to Reconcile

We can perform the same task using the INDEX and MATCH functions too.

Type the following formula in cell H5 and press ENTER.

=INDEX($C$5:$C$12,MATCH(E5,$B$5:$B$12,0))

Consequently, we have got the sales value of Coca-Cola from the central dataset (B4:C12).

Use the AutoFill feature to get the sales values for all other products.

Type the following formula in cell I5 and press ENTER.

=F5-H5

Hence, we will get the difference between the central and the current datasets for Coca-Cola.

Use the AutoFill feature to get the corresponding values for all other products.

We can see from the following image that there are variances in cells I7 and I10.

Method-03: Creating Unique Lookup Values to Reconcile Data

If there is repetition in our dataset, the VLOOKUP function returns the result based on the first match it finds.

So, there comes an inconsistent result.

We can create a unique id to overcome this issue.
Type the following formula in cell B5 and press ENTER.

=C5&D5

As a result, we got the outlet number and product name combined as a unique id.

Use the AutoFill feature to repeat the process for all other products.

Similarly, create the unique id for the current dataset too.

Type the following formula in cell B14 and hit ENTER.

=VLOOKUP(G5,$B$5:$E$12,4,FALSE)

As a result, we have got the sales value of Coca-Cola using our unique id.

Use the AutoFill feature to get the sales values for all other products.

Now, type the formula in cell C14 and press ENTER.

=J5-B14

As the difference is zero (0), we get ‘–’ as output.

Use the AutoFill feature to get the difference for all other products.
The following image shows variances in cells C16 and C19, which refer to 1Mountain Dew and 2Pepsi.

Method-04: Using Combination of ROUND And SUMIF Functions to Reconcile Data

In this case, we are provided data about four customers in cells G4:H8. We can use the ROUND and SUMIF functions to create a reconciliation formula in Excel.

We need to verify the data in cells B4:D14.
Type the following formula in cell E5 and hit ENTER.

=SUMIF($B$5:$B$14,"="&B5,$D$5:$D$14)

As a result, we get the total order amount of Customer A in cell E4.

Use the AutoFill feature to get the values for all other companies.
We get that the data is verified in terms of total order.

How to Create a Pivot Table to Reconcile Data

We can create a pivot table to reconcile data.

Our dataset looks like this.

We need to create a merged dataset first.

Select any cell from the dataset (cell B5 in this case), and go to Insert >> PivotTable.

As a result, a create PivotTable window will pop up.
Select the options as shown in the following image and click OK.

As a result, the PivotTable Fields window will appear on the right side of the active worksheet.

Drag and drop the fields in respective fields as shown in the following image.

Hence, we will get a pivot table like the following image.

Now, type the following formula in cell G24 and press ENTER.

=C24-D24

As a result, we’ll get the difference between April and May.

Use the AutoFill feature to get the values for all other products.
Hence, we’ve performed data reconciliation.

Reconciliation of Data Using Subtotal Feature

We can use the Subtotal feature to reconcile data. The sales values for outlets 1 and 2 are provided in cells F4:G6.

Select any cell from the worksheet (cell B4 in this case), and go to Data >> Subtotal to create a subtotal.

As a result, a Subtotal window will pop up.
Select the options as shown in the following image and click OK.

As a result, we have the total sales value of outlets 1 and 2.
Cross-check it with the values of cells G5 & G6.
We can see that the values match.

How to Perform Reconciliation in Bank

We need to perform reconciliations in the bank frequently. You can use the IF and COUNTIF functions to do so.

The dataset looks like the following image. Note that credits are denoted with the minus sign (-).
There are repetitive amounts of debit and credit. We need to label them as “Matched” to distinguish them.

Type the following formula in cell F5 and hit ENTER.

=IF(E5<0,-E5&"-"&COUNTIF(E$5:E5,E5),E5&"-"&COUNTIF(E$5:E5,E5))

Hence, we will get a unique amount.

Use the AutoFill feature to get the values for all other transactions.

Type the following formula in cell G5 and hit ENTER.

=IF(COUNTIF($F$5:$F$14,F5)=2,"Matched","")

As a result, we will get the match status.

Use the AutoFill feature to get the match status for all other transactions.

Things to Remember

When working on reconciliation with formula in Excel, remember these facts:

Type the formula if you’re referring to data from a pivot table. We cannot select cells from the pivot table as it will turn on the GETPIVOTDATA function.
Create a unique id whenever needed.
Set the match type as FALSE in the VLOOKUP function, as we need an exact match.

Frequently Asked Questions

1. What is the formula for reconciliation?

The reconciliation formula’s main thing is to find the similarities and dissimilarities between the two datasets. We can use the VLOOKUP, INDEX, MATCH, and SUMIF functions to reconcile data using formulas.

2. What is data reconciliation in Excel?

Data reconciliation in Excel means comparing two Excel datasets to find similarities and dissimilarities.

3. How to use a formula in Excel?

Select a cell, type =, and start typing your required formula in Excel.

Conclusion

In this article, we have demonstrated useful formulas to reconcile data in Excel. If you have followed them thoroughly, you can use these formulas to reconcile your data quickly. We hope that you’ll find help regarding your type of reconciliation here. Please let us know in the comment section if you have any further queries about the Excel reconciliation formula. Team Exceldemy will be happy to help you with this. Have a good day!

Excel Reconciliation Formula: Knowledge Hub

<< Go Back to Excel for Accounting | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!