Excel / Google Sheets Formula Builder

I use {{Excel 365 / Google Sheets}}. I need a formula for this task: {{describe data layout: columns, sample rows, expected result}}. Give me: 1) the formula in a copy-paste block; 2) plain-language explanation of each part; 3) how it handles blanks, errors (#N/A, #DIV/0!), duplicates, and case sensitivity; 4) a dynamic-array version (FILTER/XLOOKUP/LET/LAMBDA) if applicable; 5) one test case with expected output I can verify; 6) simpler alternative if my Excel version lacks functions. Do not use volatile functions unless needed. If the layout is ambiguous, list the 2 assumptions you made and the corrected formula for each.