# How to Do Reconciliation in Excel (12 Effective Techniques)

Consider the following dataset example for demonstration. Here the two datasets represent sales information for June and July of 8 products. We will perform a reconciliation of these two datasets to find mismatches.

### Method 1 – Use the VLOOKUP Function to Perform a Reconciliation in Excel

• Insert the following VLOOKUP formula in cell H5:
=VLOOKUP(E5,\$B\$5:\$C\$12,2,FALSE)

Here, as we are extracting data from the 1st dataset, therefore the column number is 2. FALSE is given to get the exact match.

• Press Enter.
• Insert this formula in cell I5 for the deduction:
=F5-H5

• Hit Enter.
• Use the AutoFill tool to apply the same formulas for other values.
• You can see that the variation between the two datasets is visible.

Negative values in the Accounting format are presented in parentheses.

### Method 2 – Reconciliation with INDEX-MATCH Functions

• Insert the following formula in cell H5:
=INDEX(\$C\$5:\$C\$12,MATCH(E5,\$B\$5:\$B\$12))

• Press Enter.
• Insert this formula to get the deducted value in cell I5:
=F5-H5

• Press Enter.
• Apply AutoFill for the cell range H6:I12.

Note: Both for VLOOKUP and INDEX-MATCH functions, if there is duplicate information with different values in the two datasets then the results will show only the first ones.

### Method 3 – Apply a Pivot Table for Reconciliation

• Combine two datasets into one.
• Since the datasets are for two different months, make a new column for the month.

• Select the cell range B4:D20.
• Go to the Insert tab and select Pivot Table.
• Choose the option From Table/Range.

• In the new dialogue box, you can see the cell range is showing to create a pivot table.
• Press OK.

• A new Pivot Table Fields panel appears.
• Place the columns named Product, Month and Sales inside the Rows, Columns and Values category, respectively.

• Insert this formula in cell F5Â in the same worksheet as the new pivot table.
=B5-C5

Note: You have to insert the cell number manually because if you click any cell for reference, it will show the GETPIVOTDATA function which will not allow you to drag the formula all over the worksheet.
• Hit Enter.
• Use the AutoFill tool to apply the same formula for the entire data series.

### Method 4 – Apply Conditional Formatting for Reconciliation

• Select the cell range B5:F12.

• Go to the Home tab and click on Conditional Formatting.

• Select Highlight Cells Rules and choose Duplicate Value from the drop-down.

• Select the condition Unique and press OK.

• You can see the different values are marked.

### Method 5 – Insert a MATCH Function for Reconciliation

• Insert this formula in cell H5:
=ISNUMBER(MATCH(F5:F12,\$C\$5:\$C\$12,0))

Here, the ISNUMBER function denotes the value, while the MATCH function compares dynamic arrays between two datasets. Lastly, insert 0 for the exact match.

• Press Enter (or Ctrl + Shift+ Enter).

### Method 6 – Reconcile with the XMATCH Function in Excel

• Insert this formula in cell H5:
=NOT(ISNUMBER(XMATCH(F5:F12,C5:C12)))

Here, The ISNUMBER function returns TRUE when a cell contains a number, and FALSE if not. This is why the NOT function is used at the beginning to get an exact match. The XMATCH function searches for the specific item among the selected cells.

• Press Enter.
• You will get the reconciliation output for all the values.

### Method 7 – Use the IF Function for Reconciliation

• Insert this formula in cell H5.
=IF(C5=F5,"TRUE","FALSE")

Here, the IF function compares the selected cells on the condition of true or false.

• HitÂ Enter.
• Use AutoFill to drag the formula in cell range H6:H12.

### Method 8 – Apply the COUNTIF Function for Reconciliation in Excel

• Insert this formula in cell H5.
=IF(COUNTIF(C5:C12,F5:F12)<>0,"True","False")

Here, the COUNTIF function counts the number of times a value, or text is contained within a range.

• Hit Enter.

### Method 9 – Reconcile Datasets with the XLOOKUP Function

• Insert this formula in cell H5.
=ISTEXT(XLOOKUP(E5,\$B\$5:\$B\$12,\$B\$5:\$B\$12))

Here, the XLOOKUP will return a corresponding value from a cell, and you can define a result if the value is not found. The ISTEXT function will fetch the texts from the dataset.

• Press Enter.
• Use the AutoFill tool to drag the formula to cell range H6:H12.

### Method 10 – Comparing Rows for Reconciliation of Datasets in Excel

• Put this formula in cell H5.
=C5=F5

• Hit Enter.
• Drag the bottom corner of cell H5 up to cell H12.
• You can see the compared results.

### Method 11 – Apply the New Window Option for Reconciliation in Excel

• Go to the View tab in worksheet Dataset 1.
• Choose New Window.

• You will get a copy of the similar workbook.
• Go to the Dataset 2 worksheet.

• In the Dataset 1 worksheet, go to the Home tab again and select Arrange All.

• In theÂ Arrange Windows dialogue box, select Vertical.
• Press OK.

• You can view the worksheets simultaneously and highlight differences among them.

### Method 12 – Use the View Side by Side Tool to Reconcile Two Workbooks

• Go to the View tab.
• Select View Side by Side under the Window group.

• You will see the other workbook in parallel.

• You can compare the datasets and highlight the differences like this:

Note: You must keep both workbooks open for this process. Otherwise, the View Side by Side will be grayed and inaccessible.

Read More: How to Reconcile Data in 2 Excel Sheets

## Related Articles

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

Get FREE Advanced Excel Exercises with Solutions!
Sanjida Mehrun Guria

Hello! Welcome to my blog. I have completed my Bachelor in Architecture from Ashanullah University of Science & Technology, Dhaka. I am a passionate, goal-oriented person with an immense thirst for knowledge and an attitude to grow continuously. Besides Architectural work, I find it very enthusiastic to work in Excel blogging. Exceldemy is a platform where I have got the opportunity to flourish my skill in Microsoft Excel and therefore, here I will be posting blogs related to... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF