# How to Do Intercompany Reconciliation in Excel – 2 Easy Methods

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

Steps:

To check whether data of 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.

• Drag down the Fill Handle to see the result in the rest of the cells.

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

• To reconcile data, considering the data of Company 1 as correct, rectify the Company 2 dataset.
• 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.
• Output2200
• VLOOKUP(B15,\$B\$6:\$F\$11,5,FALSE)-F15 → Subtracts F15 from the previous output.
2200-F15
• Output0
• Press ENTER.

This is the output.

• Drag down the Fill Handle to see the result in the rest of the cells.

• After reconciling the data, hide the Difference.

## Things to Remember

• Use an absolute reference (\$) to freeze a cell or range.

Download Practice Workbook

Download this workbook and practice.

## Related Articles

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

Get FREE Advanced Excel Exercises with Solutions!
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