Comparing Two Lists in Excel: Matches, Missing Records, Changed Values, Tolerances & Exception Reports
Find out exactly what differs between two exports – which records match, which are missing on either side, which values changed and which amounts only differ by rounding – with every cell checked as you go.
One-time payment · lifetime access · lessons, labs, exam and certificate included · no subscription.
About this course
This course compares two lists the way a reconciliation analyst does, using exports of a fictional homeware wholesaler. You flag records as in both, only in A or only in B with COUNTIF and IFS, build composite keys with & and strip prefixes with RIGHT and LEN, catch duplicates with an expanding COUNTIF range, fetch values by key with XLOOKUP and flag every changed field, accept amounts within a cent tolerance and dates within a few days using ROUND, ABS and DAYS, find references inside text with LEFT and IFERROR(FIND), summarise and explain the difference between two totals with SUMIFS and build a numbered exception report with SMALL and INDEX/MATCH.
Lessons
- Match or Missing: COUNTIF in Both Directions & Status Flags Pro
- Composite Keys: Customer & Invoice, Stripped Prefixes and False Matches Pro
- Duplicates Inside a List: COUNTIF, First Occurrences and Inflated Totals Pro
- Changed Values: XLOOKUP the Other List and Flag Each Field Pro
- Tolerance Matching: Amounts Within a Few Cents and Dates Within N Days Pro
- Partial Keys: Matching on the First Characters and Finding Numbers Inside Text Pro
- The Reconciliation Summary: Totals A vs B, Explained and Unexplained Differences Pro
- The Exception Report: A Numbered List of Every Open Item Pro