Lot & Expiry Data Backfill

I’m working with a spreadsheet that already lists each product’s name, SKU, and batch number, yet the lot numbers and expiration dates are still blank. I need those two fields filled in accurately so our accounting team can reconcile stock movements and valuation.

Here’s the key context:
• All source data lives in a single spreadsheet; no database connections are required.
• Every lot number and its corresponding expiration date must be derived strictly from the batch number—that’s the rule we follow internally.
• Logical cross-checks are essential because downstream accounting reports rely on this file; mismatched or missing entries will ripple through inventory valuation.

Deliverables I expect:
1. A clean, updated version of the spreadsheet with lot number and expiration date populated for every line item.
2. A brief note (within the file or separate) outlining the steps or formula logic you used so future audits make sense.
3. Validation flags or a summary sheet highlighting any records that didn’t meet the batch-based rule, if such cases arise.

If you’re comfortable working in Excel or Google Sheets and can apply solid reasoning to trace each batch to its proper lot and expiry, let’s do this.