Compare Two Excel Lists and Find Missing or Changed Records

A monthly vendor export, stock report, HR roster, or sales file rarely arrives in exactly the same shape as last month. Scanning two lists row by row may work for 20 records, but it is not a reliable way to reconcile thousands.

The practical answer is to compare both lists by a unique ID. Put each range in an Excel Table, use XLOOKUP to bring master values into the current list, and use a status formula to mark each row as Match, New, Changed, or Duplicate. Then use FILTER or a table filter to review only exceptions.

This guide builds that workflow first, then shows how to find records missing from the current file and when Power Query is the better choice for a recurring comparison.

Quick Solution

Assume your prior approved list is an Excel Table named tblMaster, and the latest export is a Table named tblCurrent. Both have these columns: ID, Vendor, Qty, and Rate.

1. Click anywhere in each list and press Ctrl+T to create a Table. Rename them from Table Design > Table Name.
2. In tblCurrent, add columns named Master Vendor, Master Qty, and Master Rate.
3. In the first row of each new column, enter an XLOOKUP formula.
4. Add a Status column and use the formula below.
5. Filter Status to show everything except Match.

Put this formula in the Master Vendor column of tblCurrent:

=XLOOKUP([@ID],tblMaster[ID],tblMaster[Vendor],"")

Use the same pattern for the other two returned columns:

=XLOOKUP([@ID],tblMaster[ID],tblMaster[Qty],"")

=XLOOKUP([@ID],tblMaster[ID],tblMaster[Rate],"")

Then put this in tblCurrent[Status]:

=LET(id,[@ID],IF(COUNTIFS(tblCurrent[ID],id)>1,"Duplicate ID in current",IF(COUNTIFS(tblMaster[ID],id)=0,"New",IF(COUNTIFS(tblMaster[ID],id)>1,"Duplicate ID in master",IF(AND([@Vendor]=[@[Master Vendor]],[@Qty]=[@[Master Qty]],[@Rate]=[@[Master Rate]]),"Match","Changed")))))

Important: This formula assumes ID should identify one record. Resolve duplicate IDs before treating any lookup result as a valid match.

Set Up a Reconciliation Workbook

Tables are useful here because structured references expand automatically when a new export has more rows. They are safer than repeatedly editing ranges such as $A$2:$D$5000.

Table	Purpose	Example source
tblMaster	The baseline or approved record list	Last month’s reconciled vendor list
tblCurrent	The latest list being checked	This month’s system export

Before comparing, check that the ID is genuinely stable. An invoice number, employee number, product SKU, or customer code is usually better than a name. Names can be misspelled, reformatted, or duplicated.

Worked Example: Find New and Changed Inventory Records

Suppose the master list contains these approved inventory records.

ID	Vendor	Qty	Rate
P-100	North Supply	50	12.50
P-200	Atlas Goods	80	9.75
P-300	North Supply	25	18.00

The current export contains P-100 unchanged, P-200 with quantity 85, and a new record P-400.

ID	Vendor	Qty	Rate	Expected status
P-100	North Supply	50	12.50	Match
P-200	Atlas Goods	85	9.75	Changed
P-400	Beacon Parts	10	6.25	New

The lookup columns make the reason visible. On the P-200 row, Master Qty returns 80 while the current Qty is 85. This is more useful than a simple TRUE/FALSE result when someone must review the difference.

How the status formula works

1. COUNTIFS(tblCurrent[ID],id)>1 checks whether the current export repeats the ID.
2. COUNTIFS(tblMaster[ID],id)=0 identifies an ID not found in the master list, so it is new.
3. The next COUNTIFS catches duplicate IDs in the master list. XLOOKUP returns the first match, so it should not be used to silently choose between duplicates.
4. AND(...) compares each current value with its returned master value. If all values agree, the row is a match; otherwise it changed.

LET assigns the current row’s ID to the shorter name id. It is not required, but it makes a longer formula easier to read and maintain.

For a fuller explanation of lookup behavior, see the Excel XLOOKUP complete guide.

Find Records Missing From the Current List

The current-list status finds records that are new or changed. Missing records require checking in the other direction: start in tblMaster and ask whether each master ID occurs in tblCurrent.

Add a Current Status column to tblMaster and enter:

=IF(COUNTIFS(tblCurrent[ID],[@ID])=0,"Missing from current","Present")

For example, if P-300 is in the master list but absent from the latest export, this formula returns Missing from current.

To create a separate exception report in an empty area of the worksheet, use:

=FILTER(tblMaster,tblMaster[Current Status]="Missing from current","No missing records")

FILTER returns all matching rows and updates when the source Tables change. It requires a version of Excel that supports dynamic arrays, such as Microsoft 365. Leave the cells below and to the right empty so the results have room to spill.

Compare More Than One Key

Sometimes one field is not enough. An order number may repeat by line item, or an employee name may occur in several departments. In that case, create a helper key in both Tables.

For example, if a record is unique only when Order ID and Line are combined, add a column named Key:

=[@[Order ID]]&"|"&[@Line]

Use Key in place of ID in your lookup and count formulas. The separator matters: without it, order 12 + line 34 and order 123 + line 4 would both become 1234.

If the two lists have different headers, map them before writing formulas. For example, Supplier Name may correspond to Vendor, and On Hand may correspond to Qty.

Use AI for Column Mapping, Not the Final Reconciliation

AI can help when a system export uses unfamiliar headers, but the final comparison should remain in formulas or Power Query. That gives you repeatable logic, visible exceptions, and a workbook someone else can audit.

Try this prompt with non-sensitive sample headers only:

These are headers from a master list: ID, Vendor, Qty, Rate.
These are headers from a monthly export: Product Code, Supplier Name, On Hand, Unit Price.
Propose a one-to-one column mapping, flag uncertain matches, and suggest a stable comparison key. Do not assume fields match when their business meaning differs.

Verify every proposed mapping with the report owner before refreshing your workbook. Do not paste confidential employee, customer, or financial data into an AI tool unless it is approved for that data.

Power Query Option for Repeatable File Comparisons

Best for: a monthly process where files arrive in the same general format, especially when you need to import, clean, and compare rather than paste data manually.

Load each source into Power Query with Data > Get Data > From File > From Workbook. In Power Query, set compatible data types and rename matching columns before merging.

• Use a Left Anti merge from Current to Master to return records new in the current export.
• Use a Left Anti merge from Master to Current to return records missing from the current export.
• Use an Inner merge to bring matching rows together, then add comparison columns for fields such as quantity or rate.

Power Query is usually more maintainable when this happens every month. Formula Tables are often quicker when someone needs an immediately visible row-by-row review sheet. For related lookup approaches, see how to look up and return values in Excel.

Common Problems and Fixes

Problem	Likely cause	Fix
Everything appears new	IDs are text in one list and numbers in the other, or contain spaces	Standardize the ID format and remove unwanted spaces before comparing.
A changed row looks like a match	You did not include every important field in the AND test	Add another returned master column and comparison condition.
#SPILL! from FILTER	Cells in the intended output area are not empty	Clear or move the blocking cells.
Lookup returns the wrong baseline row	Duplicate IDs exist in the master list	Investigate duplicates; do not accept the first lookup result as proof.
Dates or rates show as changed unexpectedly	Values look alike but have different underlying data types or precision	Standardize imported data types first; compare rounded values only when that matches the business rule.

Caution: Do not wrap every lookup in IFERROR just to remove errors. A missing ID is useful reconciliation evidence. If you only want to replace a not-found result, use the fourth XLOOKUP argument deliberately, as shown above.

Reconciliation Checklist

• Use a stable, unique ID or a clearly defined composite key.
• Convert both source ranges to Excel Tables.
• Check duplicates before trusting lookup results.
• Return baseline values beside current values for an auditable review.
• Check both directions: new/current exceptions and missing/master exceptions.
• Filter exceptions, investigate them, then save the reconciled workbook as the next baseline.

FAQ

Can I compare two Excel lists without XLOOKUP?

Yes. In older Excel versions, use INDEX and MATCH for the returned master values, while keeping the same COUNTIFS duplicate and missing-record checks.

Should I use conditional formatting instead?

Conditional formatting is useful for highlighting differences, but a status column is better for filtering, counting exceptions, and leaving an audit trail.

What is the best method for thousands of rows?

Excel Tables with lookup formulas work well for a one-off or review-focused comparison. Use Power Query when the import and comparison must be refreshed repeatedly from files.

Can I compare entire rows with one formula?

You can, but compare only the fields that matter to the business rule. A row may contain timestamps, notes, or formatting-related values that should not make the record fail reconciliation.