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.

## 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

- Reconciliation in Excel
- Reconcile Data
- Reconcile Two Sets of Data
- Reconcile Data in 2 Excel Sheets
- Bank Reconciliation in Excel
- Bank Reconciliation Using VLOOKUP
- Automation of Bank Reconciliation
- How to Reconcile Credit Card Statements in Excel
- Reconcile Vendor Statements
- Intercompany Reconciliation

**<< Go Back toÂ Excel for AccountingÂ | Learn Excel**