How to Do Intercompany Reconciliation in Excel (2 Easy Methods)

Excel is the most widely used tool for dealing with massive datasets. We can perform myriads of tasks of multiple dimensions in Excel. Excel has made everyone’s life easier with all its features and functions. If we want to do intercompany reconciliation in Excel, we can easily do it. In this article, I will show you 2 easy methods on how to do intercompany reconciliation in Excel.

Introduction to Intercompany Reconciliation

Intercompany reconciliation is the verification of transactions that take place in two different companies of a parent entity. For a large parent company, it is significant because the transactions must be reconciled to ensure transparency in the company. Moreover, this helps find out vital mistakes and allows for rectifying them.

How to Do Intercompany Reconciliation in Excel: 2 Easy Methods

This is the dataset for today’s article. We have some transactions in the book of Company 1 and Company 2. Some products have their Invoice No, Price per Unit, and Total price. I will reconcile these transactions now.

1. Use COUNTIF Function to Perform Intercompany Reconciliation

The first method is to use the COUNTIF function. We have to take the help of a helper column to apply this method. Let’s do it step by step.

Steps:

Suppose you want to check whether the data from Company 2 matches with Company 1 or not. In that case,

Add a Helper column in the table of Company 2.

Then, go to G15 and write down the following formula.

=COUNTIF($F$15:$F$20,F6)

Press ENTER. Excel will show the result. If both the data match, Excel will return 1. If there is any discrepancy, the output will be 0.

Then, use Fill Handle to AutoFill up to G20.

As you can see, there are two cases where output is 0, F17, and F20. That means these data do not match.

Now, you have to reconcile them. Considering the data from Company 1 is correct, I have rectified the dataset from Company 2.
Then, hide the helper column. To do so, firstly, select the entire column.

N.B. Please note that this time the helper column is showing only 1 because I have already rectified the mistakes.

Then, press CTRL+0. Excel will hide the column. After slight formatting, your final output will be like this.

2. Apply VLOOKUP Function to Do Intercompany Reconciliation

In this section, I will show another method to reconcile intercompany transactions. This time I will use the VLOOKUP function. We will not need any helper column this time.

Steps:

First of all, go to G14 and write down the following formula.

=VLOOKUP(B15,$B$6:$F$11,5,FALSE)-F15

Formula Breakdown:

VLOOKUP(B15,$B$6:$F$11,5,FALSE) → Excel will look for B15 in the array B6:F11. If it finds the value, it will return the corresponding value from the 5th column of the array.
- Output → 2200

VLOOKUP(B15,$B$6:$F$11,5,FALSE)-F15 → Subtracts F15 from the previous output.
↳ 2200-F15

- Output → 0

Now, press ENTER. Excel will return the output.

Now, use the Fill Handle to AutoFill up to G20.

Finally, after reconciling the data, hide the Difference Your dataset will look like this.

Things to Remember

Do not forget to use an absolute reference ($) to freeze a cell or range.
The VLOOKUP function only looks to the right to get data.

Download Practice Workbook

Download this workbook and practice while going through the article.

Inter-Company Reconciliation.xlsx

Conclusion

In this article, I have demonstrated 2 simple methods on how to do intercompany reconciliation in Excel. I hope it helps everyone. If you have any suggestions, ideas, or feedback, please feel free to comment below.

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

Get FREE Advanced Excel Exercises with Solutions!