Compare EXCEL Spreadsheets with XLOOKUP: Matches, Changes, Missing

When you’re applying for roles that involve reporting, finance, operations, or admin work, you will often be expected to compare two versions of a spreadsheet quickly and accurately. A common example is a month-end report where you need to confirm what changed from one export to the next. If you compare files by scrolling and guessing, you’ll waste time and you can miss important differences under pressure. A simple XLOOKUP-based method makes the process more structured and easier to review. It also creates a single “Status” column you can filter so you focus only on exceptions.

This method uses InvoiceID as the key so Excel can match the same invoice across both versions. It’s useful because it works even when rows are in a different order, since matching is based on the key and not row position. Instead of jumping between workbooks, you build one comparison table where “v1” and “v2” values sit side-by-side in the same row. Then you add a Status flag to label each row as Match, Changed, Missing in v2, or New in v2. Finally, you filter the Status column to see exactly what requires attention.

Quick Summary (3–4 bullets)

Use InvoiceID as the unique key so each record matches correctly across both spreadsheets.
Use XLOOKUP to pull v2 values next to v1 values so you can compare in one row.
Use a Status flag to label each InvoiceID as Match, Changed, Missing in v2, or New in v2.
Filter the Status column to review exceptions instead of scanning every row.

When This Method Works Best

This approach works best when each row represents one record, such as one invoice, one employee, one product, or one transaction. It also works best when you have a reliable unique identifier like InvoiceID. If the data includes multiple lines per invoice or repeated IDs, you’ll need a stronger key so the match is accurate. In many reporting workflows, this is still an easy fix by combining InvoiceID with another field such as Customer or a line number. The advantage of this method is that it’s repeatable and easy to explain, which matters in interviews and on the job. Even if you later move to Power Query, this XLOOKUP workflow is a strong foundation for clean spreadsheet comparison.

Step 1: Open Both Workbooks and Confirm the Sheet Names

Start by opening both files you want to compare. For clarity, think of them as v1 (the older version) and v2 (the newer version). Before building any formulas, confirm both workbooks use the same sheet name for the report tab. In this workflow, use MonthEndReport as the sheet name in each workbook. If one workbook uses a different sheet name, rename it so both match. This reduces errors and makes formulas easier to copy and maintain. Also confirm that the columns you plan to compare exist in both files and mean the same thing.

Step 2: Use InvoiceID as the Key and Check for Duplicates

InvoiceID is the key that allows Excel to match the same invoice across both versions. This is the most important part of the workflow because everything depends on accurate matching. Quickly check whether InvoiceID is truly unique in both files. If duplicates exist, XLOOKUP can return the wrong row because it will typically return the first match it finds. That can produce incorrect Status results even if your formulas are correct.

If you find duplicates, strengthen your key before you continue. A common fix is to create a combined key, such as InvoiceID plus Customer, or InvoiceID plus a line number if your report contains multiple invoice line items. Once your key is unique, your lookups and comparisons become reliable. This one step prevents most of the “why does this look wrong?” problems people run into when comparing spreadsheets.

Step 3: Create a Compare Sheet and Build a Master List of InvoiceIDs

In the v1 workbook, create a new sheet called Compare. Keeping results in a dedicated sheet makes the process cleaner and easier to reuse later. In column A of Compare, build a master list of InvoiceIDs to evaluate. Start by copying the InvoiceID column from v1 and pasting it into column A.

Next, you want to capture InvoiceIDs that exist only in v2, so you can detect new invoices. Copy the InvoiceID column from v2 and paste it underneath the v1 list in the same column. Now remove duplicates so each InvoiceID appears only once. The final list should include invoices from both versions, including those that only exist in one file. This master list is what allows you to label rows as Missing in v2 or New in v2 later.

Step 4: Add v1 Columns for Customer, Amount, TaxRate, and Status

Now build a clear comparison layout. A simple structure is to place v1 fields first, then v2 fields next to them. For example, put v1 Customer, v1 Amount, v1 TaxRate, and v1 Status in columns B through E. Then put the same fields for v2 in columns F through I. This side-by-side layout is what makes the comparison fast to review.

To pull a value from v1 based on InvoiceID, use XLOOKUP in the v1 columns. Example formula:

=XLOOKUP($A2,'V1'!$A:$A,'V1'!$B:$B,"")

It looks up the InvoiceID in cell A2 within column A on the V1 sheet and returns the matching value from column B, or returns blank if no match is found. Copy that pattern for each field, changing the return column as needed. Using blanks for missing values keeps later logic clean and avoids distracting error messages.

Step 5: Add v2 Columns Using XLOOKUP

Now add v2 columns that pull matching values using XLOOKUP from the v2 sheet. The goal is to place v2 values on the same row as the corresponding v1 values for the same InvoiceID. This is what eliminates manual searching and window switching. Example pattern:

=XLOOKUP($A2,'V2'!$A:$A,'V2'!$C:$C,"")

Use the same approach for Customer, Amount, TaxRate, and Status, each returning the correct column from v2. Once this is set up, each row becomes a complete “before and after” view for that InvoiceID. This is usually much faster to validate than trying to compare two separate reports.

Step 6: Add “Exists” Checks for v1 and v2

Before labeling rows as Match or Changed, it helps to know whether the InvoiceID exists in each version. Add two helper columns: one for “Exists in v1” and one for “Exists in v2.” You can do this with MATCH wrapped in ISNUMBER.

Example for v1:

=ISNUMBER(MATCH($A2,'V1'!$A:$A,0))

It checks whether the InvoiceID in A2 exists in the V1 sheet’s column A, returning TRUE if found and FALSE if not. Create the same formula for v2, pointing it to the v2 InvoiceID column. These helper columns are useful for both the Status logic and for troubleshooting when something looks off.

Step 7: Create the Status Flag Column (Match / Changed / Missing / New)

Now build the Status column that makes this workflow easy to filter and review. The logic is:

If the ID exists in both v1 and v2, check whether all compared fields match.
If they all match, label the row Match.
If any field differs, label the row Changed.
If the ID exists only in v1, label it Missing in v2.
If the ID exists only in v2, label it New in v2.

Use this formula (shown in a readable multi-line format):

=IF(
AND($J2,$K2),
IF(
AND($B2=$F2,$C2=$G2,$D2=$H2,$E2=$I2),
"Match",
"Changed"
),
IF(
$J2,
"Missing in v2",
"New in v2"
)
)

It labels each InvoiceID as Match if it exists in both versions and all compared fields match, Changed if it exists in both but any field differs, Missing in v2 if it’s only in v1, or New in v2 if it’s only in v2. Once this column is working, you have the core of your comparison tracker. This is also the column you will filter to see only exceptions.

Step 8: Add a “What Changed” Column for Faster Review

A “Changed” label is helpful, but it’s even better when you can see what changed without scanning across several columns. Add a helper column that lists which fields changed, and only show that list when Status is Changed. Use this formula (readable multi-line format):

=IF(
$L2<>"Changed",
"",
LEFT(
IF($B2<>$F2,"Customer, ","") &
IF($C2<>$G2,"Amount, ","") &
IF($D2<>$H2,"TaxRate, ","") &
IF($E2<>$I2,"Status, ",""),
LEN(
IF($B2<>$F2,"Customer, ","") &
IF($C2<>$G2,"Amount, ","") &
IF($D2<>$H2,"TaxRate, ","") &
IF($E2<>$I2,"Status, ","")
)-2
)
)

It lists which fields changed (Customer, Amount, TaxRate, Status) when the row is marked Changed, and otherwise returns blank. This makes your review quicker because you can spot the reason for the change immediately. It also makes handoffs easier if you need to send the exceptions list to someone else.

Step 9: Filter the Status Column to Review Exceptions Only

Now that your Status column is populated, filter it to show only what needs action. In many cases, that means filtering out Match and focusing on:

Changed
Missing in v2
New in v2

This turns a large report into a short, clear worklist. You can review each exception, confirm whether the change is expected, and investigate anything that looks wrong. If your goal is to reconcile two exports, this filtered list is usually the fastest path to completion. If your goal is reporting, you can copy the filtered results into a separate “Exceptions” sheet for a clean summary.

Practical Tips to Avoid Common Comparison Errors

If you see unexpected “Changed” results that don’t look real, the issue is often formatting or data types. One file may store numbers as text, or tax rates may be rounded differently. In those cases, standardize formatting so comparisons behave consistently. Also watch for extra spaces in text fields like Customer, since an invisible trailing space can cause a mismatch. If blanks matter in your workflow, decide whether blank and zero should be treated as different or equivalent before you rely on the output. A quick spot-check of a handful of rows can help you confirm the tracker is behaving the way you expect. Once validated, the same tracker can typically be reused month after month.

Benefits of This Tracker

This tracker is reusable, which makes it a good investment for recurring reporting work. You spend a few minutes building columns and formulas, but then you can reuse the same Compare sheet every month.

Once it’s set up, you typically just replace the two input files and refresh the comparisons by recalculating. It saves time because you stop reviewing rows that match and focus only on exceptions. It can also reduce mistakes because you avoid manual scanning and you have a structured way to confirm what changed.

Summary: The Full Workflow in One View

First, make sure both files use the same sheet name and that InvoiceID is a clean, unique key. Next, create a Compare sheet and build a master InvoiceID list that includes IDs from both v1 and v2.

Then use XLOOKUP to pull v1 and v2 fields side-by-side so each row represents one InvoiceID comparison. Add “Exists” checks, then use a Status flag formula to label rows as Match, Changed, Missing in v2, or New in v2.

Finally, add a “What Changed” column so you can see differences quickly, and filter the Status column to review only exceptions. This gives you a repeatable, interview-friendly method for comparing spreadsheets under real-world time pressure.

Practice Assessment Tests

Free Assessment Tests To Get You Prepared