The reason to care: every floating lookup table you leave on a helper tab is a range that can drift, get cleared in a Ctrl+A accident, or quietly break when someone inserts rows above it. Embedding the table in the formula removes that surface area entirely.
Array Literal Syntax Reference
Google Sheets uses 2 delimiter types:
| Delimiter | Function | Example | Result |
|---|---|---|---|
, (comma) | Next column, same row | {1,2,3} | 1 row × 3 columns |
; (semicolon) | Next row | {1;2;3} | 3 rows × 1 column |
| Both | 2D array | {"Q1","Q2";"Jan","Apr"} | 2 rows × 2 columns |
Every row must have the same number of values. {1,2;3} throws #ERROR! immediately - Sheets won't guess what you meant.
Mixing types within the same literal - numbers and text together - works syntactically but coerces everything to text. Numeric values lose their arithmetic behavior downstream. Keep arrays homogeneous unless you're feeding them directly to VLOOKUP or INDEX where the column lookup isolates the type.
Five Places Array Literals Pay Off in a Multi-Tab Model
Scenario rate lookups without a helper range. Rather than a 3-row WACC table sitting in a corner of your Assumptions tab:
=VLOOKUP(Assumptions!$B$2, {"Conservative",0.082;"Base",0.097;"Upside",0.114}, 2, FALSE)
Anyone opening this formula sees the rate schedule immediately. No hunting for which cell holds 9.7%. The trade-off: if you use this pattern in 12 cells across your model and the Base WACC changes, you're doing find-and-replace in 12 places. For a 3-scenario × 1-variable table, that's manageable. For a full sensitivity grid, use a named range instead.
Scenario labels without a toggle column. Every board pack has scenario headers. Skip the helper column:
=INDEX({"Conservative","Base Case","Upside"}, Assumptions!$B$1)
Where $B$1 is a 1/2/3 switch. There's no editable helper cell for someone to accidentally overwrite mid-presentation.
Line item classification flags. When you want to flag specific P&L rows for subtotals or conditional formatting:
=ISNUMBER(MATCH('P&L'!B2, {"Revenue","Gross Profit","EBITDA","EBIT","Net Income"}, 0))
Wrap in ARRAYFORMULA() down B2:B200 and you get a TRUE/FALSE flag column for every labeled subtotal row - no classification table, no additional tab.
Fixed weights in SUMPRODUCT. When segment mix is an assumption, not live data:
=SUMPRODUCT('Segment'!C2:C4, {0.55;0.30;0.15}) / SUMPRODUCT('Segment'!D2:D4, {0.55;0.30;0.15})
The weights are visible in the formula. An auditor reviewing this 6 months from now doesn't need to chase down where 55% came from.
Period selection masks. Sum only Q1 columns from a full-year monthly row:
=SUMPRODUCT('P&L'!C2:N2 * {1,1,1,0,0,0,0,0,0,0,0,0})
Blunt, but fully auditable. No OFFSET, no INDIRECT, no volatile recalculation.
The Locale Problem That Bites Multinational Teams
This is the issue the official help page buries. Google's documentation notes that delimiter characters shift by locale. In regions where a comma is the decimal separator - Germany, France, most of continental Europe - Google Sheets changes the array literal delimiters:
- Column separator becomes
\(backslash) instead of, - Row separator stays
;
The WACC formula from above looks like this in a German-locale Sheet:
=VLOOKUP(Assumptions!$B$2, {"Conservative"\0.082;"Base"\0.097;"Upside"\0.114}, 2, FALSE)
A formula that parses correctly in a US-locale workbook throws #ERROR! when a German colleague opens it, because Sheets treats the comma as a decimal character rather than a column delimiter. The formula looks syntactically fine in the formula bar - the error only surfaces when someone with a different locale setting opens the file.
As of June 2026, Google has not implemented a locale-independent array literal syntax. If your team spans multiple locales, use named ranges for any lookup table that crosses language settings. Array literals are safe for single-locale teams and personal workbooks.
ARRAYFORMULA's Curly Braces Are Not Array Literals
A real source of confusion for analysts coming from Excel.
In Excel, entering a formula with Ctrl+Shift+Enter wraps it in {} and forces array evaluation. That's an array formula - different from a literal. In Google Sheets, Ctrl+Shift+Enter does nothing special. You use ARRAYFORMULA() as a regular function wrapper instead, and the curly braces in Excel muscle memory don't carry over.
Array literals {1,2,3} define data inline. ARRAYFORMULA() tells Sheets to evaluate a formula element-wise across a range. You can combine them:
=ARRAYFORMULA('P&L'!C2:C200 * {0.30,0.30,0.40})
Here {0.30,0.30,0.40} is the literal; ARRAYFORMULA handles the row-by-row broadcast. But they're separate constructs doing separate jobs.
Performance: When to Stop Using Literals
For arrays under 50 elements used in standard lookup or INDEX formulas, there's no measurable performance difference between array literals and named ranges. Sheets handles both equivalently.
The calculus changes in heavy recalculation scenarios. If your model has 500+ SUMPRODUCT calls - not uncommon in a monthly LBO with detailed debt schedules - literals embedded in each formula get re-parsed on every recalculation cycle. A named range pointing to a fixed helper table gets evaluated once and cached at the name level. In a model already taking 8+ seconds to recalculate, that difference adds up. For a standard 8-tab board pack model, you won't notice either way.
The practical rule: use array literals when the array appears in 1-3 places and has fewer than 20 elements. Extract to a named range when it appears in 4+ formulas or when you're working on a shared model across locales.
If you're auditing a model you've inherited and want to quickly understand where array literals are being used across tabs, ModelMonkey's AI assistant can scan formula structure in plain English and surface the patterns - faster than manually opening 50 formula bars.
Try ModelMonkey free for 14 days - it works in both Google Sheets and Excel.