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.

how to do intercompany reconciliation in excel


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)

how to do intercompany reconciliation in excel

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

how to do intercompany reconciliation in excel

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

how to do intercompany reconciliation in excel

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

how to do intercompany reconciliation in excel

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.
    • Output2200
  • VLOOKUP(B15,$B$6:$F$11,5,FALSE)-F15 → Subtracts F15 from the previous output.
    2200-F15
    • Output0
  • Now, press ENTER. Excel will return the output.

how to do intercompany reconciliation in excel

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

how to do intercompany reconciliation in excel


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.

Akib
We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo