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.

The sample dataset showcases transactions in the book of Company 1 and Company 2. Products have Invoice No, Price per Unit, and Total price.

Method 1 – Using the COUNTIF Function to Perform Intercompany Reconciliation

Use the COUNTIF function.

Steps:

To check whether data from Company 2 matches Company 1:

Add a Helper column to the table of Company 2.

Go to G15 and enter the following formula.

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

Press ENTER. If there’s a data match, Excel will return 1. If there is any discrepancy, the output will be 0.

Use Fill Handle to AutoFill cells to G20.

In F17, and F20 the output is 0, as thereis no match.

To reconcile data, considering the data from Company 1 is correct, rectify the dataset from Company 2.
To hide the helper column, select it.

Press CTRL+0.

This is the output.

Method 2 – Applying the VLOOKUP Function to perform Intercompany Reconciliation

Steps:

Go to G14 and enter the following formula.

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

Formula Breakdown:

VLOOKUP(B15,$B$6:$F$11,5,FALSE) → looks 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