How to Compare Data in Two Excel Sheets Using VLOOKUP

As a business owner, discrepancies in data can be a major headache. Imagine your accountants, Sara and James, each present you with different versions of customer payment data in Excel. How do you efficiently compare these two sheets and reconcile the information to ensure accuracy? The answer lies in leveraging Excel’s powerful VLOOKUP function. This article will guide you through a step-by-step process to Compare Data In Two Excel Sheets, helping you quickly identify matches and discrepancies.

Step 1: Organize Your Data in Two Excel Sheets

Begin by copying each dataset into separate sheets within a single Excel file. For this example, we’ll name the sheets “Sara’s Data” and “James’ Data”. Crucially, ensure both datasets have identical column headers, even if the data within those columns differs. This consistent structure is essential for effective comparison.

Step 2: Prepare the First Sheet for VLOOKUP Formulas

Navigate to your first sheet (in our case, “Sara’s Data”). We will utilize the VLOOKUP function (or XLOOKUP if you are using Excel 365 or a later version) to fetch corresponding data from the second sheet (“James’ Data”) for comparison.

To effectively use VLOOKUP, you need a unique identifier column present in both sheets. Common examples include “Customer ID”, “Invoice Number”, or a similar unique reference.

Insert a new column adjacent to your data in the first sheet. In this new column, we will input the VLOOKUP formula to retrieve the corresponding values from the second sheet based on the unique identifier. Your sheet setup should resemble the image below.

Step 3: Implement the VLOOKUP Formula

In the newly added column of your first sheet, enter the VLOOKUP formula. Assuming your unique identifier is in column B and you are starting from row 3, the formula would look like this:

=VLOOKUP(B3,'James Sheet'!$B$3:$C$32,2,FALSE)

Understanding the VLOOKUP Formula:

The VLOOKUP formula is designed to search for a specific value in the first column of a range and return a corresponding value from another column in the same row. Let’s break down each part of our formula:

VLOOKUP(B3: This initiates the VLOOKUP function and specifies B3 as the lookup value. In this case, it’s the Customer ID in cell B3 of “Sara’s Data”.
,'James Sheet'!$B$3:$C$32: This defines the table array, which is the range of cells in the second sheet (“James’ Data”) where VLOOKUP will search for the lookup value and retrieve the corresponding data. 'James Sheet'! specifies the sheet name. $B$3:$C$32 is the absolute cell reference for the data range in “James’ Sheet”. We use absolute references ($) so that the range remains fixed when you copy the formula down. It’s important to include the unique identifier column (Column B) and the column you want to compare (Column C – “Amount Paid” in this example).
,2: This is the col_index_num, indicating which column in the table array contains the value to be returned. 2 means we want to retrieve the value from the second column of our table array ($B$3:$C$32), which is the “Amount Paid” column in “James’ Data”.
,FALSE): This is the range_lookup argument. FALSE specifies that we want an exact match for the lookup value. This is crucial for accurate data comparison. VLOOKUP will only return a value if it finds an exact match for the Customer ID in the second sheet.

For users of Excel 365 or later, XLOOKUP offers an improved alternative with more flexibility. The equivalent XLOOKUP formula would be:

=XLOOKUP(B3,'James Sheet'!$B$3:$B$32,'James Sheet'!$C$3:$C$32, "ID Missing")

XLOOKUP(B3: The lookup value (Customer ID in B3).
,'James Sheet'!$B$3:$B$32: The lookup array, the column in “James’ Data” to search for the Customer ID (Column B).
,'James Sheet'!$C$3:$C$32: The return array, the column in “James’ Data” from which to return the corresponding value (Column C – “Amount Paid”).
,"ID Missing"): The if_not_found argument. If XLOOKUP doesn’t find a matching Customer ID, it will return “ID Missing”.

After entering the formula in the first cell of your new column, drag the fill handle (the small square at the bottom right of the cell) down to apply the formula to all rows in your data. This will populate the new column with the corresponding “Amount Paid” values from “James’ Data” for each Customer ID in “Sara’s Data”.

If a Customer ID from “Sara’s Data” is not found in “James’ Data”, VLOOKUP will return a #N/A error. XLOOKUP, using the formula above, will display “ID Missing” in such cases, which is more user-friendly.

Step 4: Reconcile and Identify Discrepancies

Now that you have retrieved the corresponding values from the second sheet in your first sheet, the next step is to reconcile the data and pinpoint any differences. We can achieve this using Excel’s IF function.

In another new column, enter the following IF formula:

=IF(ISERROR(D3),"ID Missing", IF(D3<>C3,"Not matching", "Matching"))

IF(ISERROR(D3), "ID Missing": This part first checks if the VLOOKUP result in cell D3 (the column containing VLOOKUP formulas) is an error (#N/A). If it is an error, it means the Customer ID was missing in the second sheet, and the formula will display “ID Missing”.
, IF(D3<>C3, "Not matching", "Matching")): If there is no error (meaning VLOOKUP found a Customer ID), this nested IF function compares the “Amount Paid” value from “James’ Data” (D3) with the “Amount Paid” value from “Sara’s Data” (C3).
- If D3<>C3 (they are not equal), the formula displays “Not matching”, indicating a discrepancy.
- If D3=C3 (they are equal), the formula displays “Matching”, indicating the data is consistent across both sheets.

Drag the fill handle down to apply this formula to all rows. This will populate the reconciliation column with “Matching”, “Not matching”, or “ID Missing” labels, clearly highlighting the data comparison results.

Excel’s filtering feature can now be used to easily isolate “Not matching” or “ID Missing” records for further investigation. Click on the filter dropdown in the reconciliation column header and select the values you want to focus on.

Step 5: Visually Highlight Discrepancies with Conditional Formatting (Optional)

For a quick visual overview of discrepancies, conditional formatting can be applied to automatically highlight “Not matching” and “ID Missing” records.

Select Data Range: Select the entire data range you want to format (e.g., B3:E32, including Customer ID, Amount Paid from both sheets, and the Reconciliation column).
Open Conditional Formatting: Go to the “Home” tab on the Excel ribbon, and in the “Styles” group, click on “Conditional Formatting” and select “New Rule…“.
Create Formula-Based Rule: In the “New Formatting Rule” dialog box, select “Use a formula to determine which cells to format”.
Enter Formula for “Not matching”: In the “Format values where this formula is true” box, enter the formula: =$E3="Not matching" (assuming your reconciliation column is column E and data starts from row 3).
Set Formatting: Click the “Format…” button, choose a formatting style (e.g., fill color, font color) to highlight “Not matching” records, and click “OK”.
Repeat for “ID Missing”: Click “New Rule” again and repeat steps 3-5, this time using the formula: =$E3="ID Missing" and choosing a different formatting style.
Click OK: Click “OK” in the “New Formatting Rule” dialog box to apply the conditional formatting.

Now, all “Not matching” and “ID Missing” records will be visually highlighted with the formatting you selected, making it incredibly easy to spot data inconsistencies.

Free Excel Comparison Template

To streamline your data comparison process, you can download a pre-built Excel template that incorporates these techniques. Download the free comparison template here.

Alternative Methods for Excel Data Comparison

While VLOOKUP is a highly effective method, Excel provides other tools for comparing spreadsheets, including:

Excel’s Compare Files Feature: Found under the “Review” tab, this feature can highlight differences between two workbooks, although it may not be as granular for data reconciliation as VLOOKUP.
Power Query (Get & Transform Data): Power Query offers advanced data manipulation and comparison capabilities, especially useful for complex datasets or automated comparisons.
Specialized Excel Add-ins: Various third-party add-ins provide dedicated features for spreadsheet comparison and data reconciliation.

Conclusion

Comparing data across two Excel sheets doesn’t have to be a tedious manual task. By mastering the VLOOKUP (or XLOOKUP) function and combining it with IF formulas and conditional formatting, you can efficiently reconcile data, identify discrepancies, and ensure data accuracy. This step-by-step approach provides a practical solution for anyone needing to compare and validate information across multiple Excel spreadsheets.