수식을 붙여넣은 후 Google 스프레드시트에서 수식 구문 분석 오류: 쉼표 vs 세미콜론

You paste a VLOOKUP from a tutorial and the cell shows #ERROR!. The tooltip says Formula parse error. The function name is spelled right. The problem is the commas.

Why it happens

The argument separator belongs to the spreadsheet's locale, not to whoever wrote the tutorial, and not to your Google account language. US and UK sheets separate arguments with commas. Locales that use a comma as the decimal mark (Germany, France, Brazil and many others) separate arguments with semicolons, because a comma can't mean both "decimal" and "next argument".

Reproduce it in a minute

1. New blank sheet. File > Settings > Locale, pick Germany, then reload the spreadsheet so the locale applies.
2. Type 101 in A2, Widget in B2 and 101 in D2.
3. In E2 paste:

=VLOOKUP(D2,A2:B2,2,FALSE) 

E2 shows #ERROR! with Formula parse error. Try =IF(D2=101,B2,"none") too. Same error, so it isn't a VLOOKUP bug. Any function with more than one argument hits it.

Don't test this with =SUM(1,2). On a German locale that can read as the decimal number 1,2 and never error.

The fix

Use the separator the locale expects:

=VLOOKUP(D2;A2:B2;2;FALSE) =IF(D2=101;B2;"none") 

Both return Widget. If you'd rather keep commas, set File > Settings > Locale to United States and reload.

Two things not to do: changing your Google account language won't help, and wrapping the formula in IFERROR only hides a formula that never ran.

Watch out for

• Pasted text isn't translated. A formula you paste or type as text keeps its commas, so on a semicolon locale you have to type the separators that locale expects.
• Array literals are different. Inside { } on a semicolon locale, a US comma between columns becomes a backslash: {A2\B2} is two columns and {A2;B2} stacks rows. Don't blindly swap commas for semicolons there.
• It's not #NAME?. That one means an unknown function name, like VLOKUP.
• It's not QUERY's "Unable to parse query string". That's a #VALUE! inside the query text itself.

---

Originally published on SheetError, a free library of Google Sheets error fixes (I run it).

This article was created with the help of AI.