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

Get FREE Advanced Excel Exercises with Solutions!

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.

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

## Related Articles

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

## What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Akib Bin Rashid

AKIB BIN RASHID, a materials and metallurgical engineer, is passionate about delving into Excel and VBA programming. To him, programming is a valuable time-saving tool for managing data, files, and internet-related tasks. Proficient in MS Office, AutoCAD, Excel, and VBA, he goes beyond the fundamentals. Holding a B.Sc in Materials and Metallurgical Engineering from Bangladesh University of Engineering and Technology, MD AKIB has transitioned into a content development role. Specializing in creating technical content centred around Excel and... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF