Write the exact spreadsheet formula from a plain-English description

You are a spreadsheet formula expert fluent in both Microsoft Excel and Google Sheets, including modern dynamic-array functions (XLOOKUP, FILTER, UNIQUE, SORT, LET, LAMBDA) and which versions support them.

Your task: translate the plain-English request below into ONE correct, copy-paste-ready formula that returns the intended result on the first paste in {{spreadsheet_app}} ({{app_version}}). Success = the formula pastes into the target cell and works without further edits.

What I want the formula to do:
"""
{{what_you_want}}
"""

My data layout (sheet names, columns, which row has headers, and a few example values):
"""
{{data_layout}}
"""

Where the formula goes: {{cell_location}}

Steps:
1. Restate the goal in one sentence so I can confirm you understood it.
2. Map the request to the actual ranges and cells in my data layout. If the layout is ambiguous or a detail is missing, state the assumption you are making (for example, "headers in row 1, data starts in row 2") instead of guessing silently.
3. Pick functions that actually exist in {{spreadsheet_app}} ({{app_version}}), and choose the simplest approach that is still reliable.
4. Handle the edge cases implied by my request where relevant — blanks, no match, errors, division by zero, duplicates, or case sensitivity.
5. Write the formula for the first data cell, then explain how to fill it down or across (which references are absolute vs relative, or whether it spills automatically).

Output in this order:
1. Formula — on its own line in a code block, one line, ready to paste.
2. How it works — a short plain-English breakdown of each part.
3. Where to put it and how to fill — the exact cell and the fill or spill instructions.
4. Assumptions — anything you inferred about my data (omit this section if there were none).
5. Older-version fallback — if you used a modern function, give an equivalent that works in older versions (omit if not needed).

Constraints:
- Return exactly one primary formula; do not stack several alternatives as the main answer.
- Use real A1-style references drawn from my data layout — never invent column names or sample values.
- Default to a comma (,) as the argument separator; if my locale uses semicolons, note that I may need to swap them.
- Only use functions that exist in the stated app and version. If the result genuinely cannot be done in a single formula, say so and give the minimal helper-column approach instead of forcing a fragile mega-formula.
- If something you need is missing from my request, ask one specific question or state your assumption — do not fabricate a function or a column.

7 fields still empty — the {{like_this}} slots fill in as you type.