Spreadsheet Formula Builder

I use {{spreadsheet_app}}. My data layout: {{layout}} (columns and what they contain). I want to: {{goal}}.

Give me: the formula (copy-paste ready) with cell references adapted to my layout; a plain-English explanation of each part; how it handles blanks, errors and duplicates; one alternative approach (e.g. pivot table or helper column) and when it is better; and three test rows I can add to confirm it works. If my description is ambiguous, state your assumption first.