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.

Download Practice Workbook

Download this workbook and practice while going through the article.

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.

2 Easy Methods to Do Intercompany Reconciliation in Excel

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.

• 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.

Read More: How to Reconcile Two Sets of Data in Excel (9 Simple Ways)

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.

• 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.

Read More: How to Reconcile Vendor Statements in Excel (2 Easy Methods)

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.

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. Please visit Exceldemy for more useful articles like this.

Related Articles

• How to Create a Party Ledger Reconciliation Format in Excel
• How to Do Bank Reconciliation in Excel (with Easy Steps)
• Automation of Bank Reconciliation with Excel Macros