Why does a formula from a colleague give #NAME??

Because it was written for an Excel in another language: function names are translated, and the argument separator changes (comma in English, semicolon in French).

The #NAME? error

Excel does not recognise a word in the formula. A frequent cause: a formula copied from a colleague whose Excel is in another language, RECHERCHEV instead of VLOOKUP.

What it means

#NAME? means a name (of a function, a named range, a table) does not exist for Excel.

Where it comes from

A function in another language: RECHERCHEV, SOMME.SI or SIERREUR in an English Excel, VLOOKUP in a French one.
A typo: SUMM, VLOKUP.
Text without quotes: =IF(A1=Yes,1,0) instead of =IF(A1="Yes",1,0).
A function too recent for the Excel version: XLOOKUP, FILTER, UNIQUE or TEXTJOIN in Excel 2016. The file then shows _xlfn. before the name.
A deleted or misspelled defined name.
A missing colon in a range: A1A10 instead of A1:A10.

Fixing it

Translate the formula: Translate a formula moves names and separators from one language to the other.
Check the spelling: as you type =VLO, Excel suggests existing functions.
Put texts between double quotes.
For a recent function that is missing: replace it (INDEX and MATCH instead of XLOOKUP).

Tools that help

Translate a formula: VLOOKUP to RECHERCHEV and back, separators and decimals included.
Explain a formula: flags unknown functions and the number of arguments.
Find the right formula: the INDEX and MATCH version, which works in every version.

Frequently asked questions

Why does a formula from a colleague give #NAME??: Because it was written for an Excel in another language: function names are translated, and the argument separator changes (comma in English, semicolon in French).