Build a spreadsheet formula from a plain description

Write a spreadsheet formula for me.

Spreadsheet program: {{app:Google Sheets}}
What I want the formula to do: {{goal}}
How my data is laid out (which columns hold what, which row the data starts on, the sheet name if it matters): {{layout}}
A few example rows, if I have them: {{examples:none provided}}

Give me:
1. The formula, ready to paste, in a code block.
2. The cell to put it in, and whether to drag it down or whether it fills by itself.
3. An explanation of each part from the inside out, in plain words.
4. What it returns for my example rows, or for a small example you make up if I gave none.
5. What will break it: blank cells, text stored as numbers, extra spaces, merged cells, dates stored as text. Give the fix for each one that applies to my case.
6. A simpler or more robust alternative, if there is one. For example, a lookup that does not break when a column is inserted.

Rules:
- Use functions that exist in the program I named. If the best function is only in newer versions, say so and give a fallback.
- Use my real column letters and sheet names, not placeholders.
- Assume my regional settings use {{separator:commas}} to separate arguments.
- If my description could mean two different things, show both formulas and explain the difference.
- If this would be better done with a pivot table, a filter, or a helper column than with one giant formula, tell me.