Comparing Two Lists in Excel: Matches, Missing Records, Changed Values, Tolerances & Exception Reports

What you'll build

What you will be able to do

Each lab ends with a concrete result that you build yourself and the checker confirms, cell by cell.

• Compare Kestrel's invoiced orders with the orders the warehouse shipped, count every order number in the other list, flag each record as in both, only in A or only in B, and count each status.
• Build a customer|invoice key on both sides, strip the INV- prefix from the statement references with RIGHT and LEN, and show that matching on the invoice number alone hides two missing invoices.
• Find invoices that appear more than once in a payments export, mark first occurrences and repeats with an expanding range, and work out the distinct invoice count and the total with each invoice counted once.
• Fetch this month's price for every SKU with XLOOKUP, flag which fields changed, count the changed fields per SKU and count the SKUs with any change.
• Round the differences between ledger and statement to cents, accept amounts within ± 0.05 and dates up to three days apart, and count the lines that match within both tolerances.
• Cut variant codes down to their base code with LEFT, look the base codes up in the catalogue, check with IFERROR(FIND(…)) whether a bank text mentions the expected invoice and count the payments that can be matched automatically.
• Summarise a comparison by status with COUNTIFS and SUMIFS, compare the two totals, split the difference into missing lines and amount differences, show what is left unexplained and chart A against B by status.
• Mark every unmatched line in a helper column, pull the exceptions into a numbered report with SMALL and INDEX/MATCH, and show the difference that each open item contributes.