How to Compare Two Excel Sheets for Differences Using VLOOKUP

Comparing data across two Excel spreadsheets can be tedious. Whether reconciling financial reports or merging customer lists, identifying discrepancies is crucial. This guide provides a step-by-step approach on how to compare two Excel sheets for differences using the powerful VLOOKUP function. We’ll cover everything from setting up your data to highlighting discrepancies using conditional formatting.

Setting Up Your Data for Comparison

Before diving into VLOOKUP, ensure your data is organized correctly. Copy the data from both sources into separate sheets within the same Excel workbook. Crucially, both sheets must have at least one common column with unique identifiers, such as a Customer ID, Invoice Number, or Employee ID. This shared column will be the foundation for our comparison. For this example, we’ll use two sheets: “Sara’s Sheet” and “James’ Sheet,” each containing customer IDs and payment amounts.

Utilizing VLOOKUP to Find Matching Values

Choose Your Starting Sheet: Begin with either sheet. In our case, let’s start with “Sara’s Sheet.”
Insert a VLOOKUP Column: Add a new column next to the data you want to compare. This column will house the VLOOKUP formula results.
Construct the VLOOKUP Formula: In the first cell of your new column, enter the following VLOOKUP formula:
```
=VLOOKUP(B3,'James Sheet'!$B$3:$C$32,2,FALSE) 
```
Let’s break down this formula:
- B3: This refers to the cell containing the unique identifier (Customer ID) in “Sara’s Sheet” that you want to look up in “James’ Sheet.”
- 'James Sheet'!$B$3:$C$32: This specifies the table array or range in “James’ Sheet” where VLOOKUP will search for a match. The $ symbols create absolute references, ensuring the range remains constant when copying the formula.
- 2: This indicates that VLOOKUP should return the value from the second column of the table array (in this example, the “Amount Paid” column).
- FALSE: This argument forces VLOOKUP to find an exact match.

Using XLOOKUP (Excel 365 Users): If you have Excel 365, you can leverage the more versatile XLOOKUP function:
```
=XLOOKUP(B3,'James Sheet'!$B$3:$B$32,'James Sheet'!$C$3:$C$32, "ID missing")
```

Apply the Formula: Drag the fill handle (the small square at the bottom right of the cell) down to apply the formula to all rows. This will populate the new column with corresponding values from “James’ Sheet.”

Reconciling and Identifying Differences

Compare with an IF Statement: In another new column, use an IF formula to compare the original value in “Sara’s Sheet” with the VLOOKUP result:
```
=IF(ISERROR(D3),"ID Missing", IF(D3<>C3,"Not matching", "Matching"))
```
This formula checks for three scenarios:
- ID Missing: If VLOOKUP returns an error (e.g., #N/A), it indicates the ID is missing in “James’ Sheet.”
- Not Matching: If the values are different, it flags them as “Not matching.”
- Matching: If the values are identical, it marks them as “Matching.”

Filter for Discrepancies: Apply filters to this new column to easily isolate “Not matching” or “ID Missing” entries for further investigation.

Highlighting Differences with Conditional Formatting

For enhanced visibility, use conditional formatting to highlight discrepancies:

Select the Data: Select the entire data range, including the reconciliation column.
Apply Conditional Formatting Rules: Go to Home > Conditional Formatting > New Rule. Create two rules:
- Rule 1 (Not Matching): Use a formula: =$E3="Not matching" and choose a formatting style (e.g., fill with yellow).
- Rule 2 (ID Missing): Use a formula: =$E3="ID Missing" and choose a different formatting style (e.g., fill with red).

Conclusion

By using VLOOKUP in conjunction with IF statements and conditional formatting, you can efficiently compare two Excel sheets, pinpoint differences, and streamline your data reconciliation process. This technique empowers you to identify and address discrepancies with accuracy and ease.