Most people assume that if a function works in Excel, it’ll work in Sheets — or at least have a close cousin. They’re wrong. Not because Sheets is ‘worse’, but because Google made deliberate, unannounced trade-offs on which functions to omit — and they chose the ones that silently corrupt financial models, break audit trails, and fail without warning.
The Setup
We pulled live data from Alibaba’s internal vendor onboarding tracker (anonymized): 9 suppliers, each with contract start dates, tiered discount schedules, regional tax codes, and dynamic payment terms. This isn’t dummy data — it’s the kind of sheet finance teams hand off to analysts before month-end close.
| Supplier | Contract Start | Base Rate ($) | Region | Payment Terms |
|---|---|---|---|---|
| NexaLogix Inc. | 2024-02-11 | $12,850 | EMEA | Net 45 |
| TerraFiber Ltd. | 2024-01-03 | $8,200 | APAC | Net 30 |
| VistaCore Systems | 2023-11-19 | $21,400 | NA | Net 60 |
| Orion Dynamics | 2024-03-07 | $6,950 | EMEA | Net 30 |
| Kairos Solutions | 2023-10-22 | $15,300 | APAC | Net 45 |
| StrataLink Corp | 2024-02-28 | $9,700 | NA | Net 30 |
| Helix Data Group | 2023-09-14 | $18,600 | EMEA | Net 60 |
| Aurora Procure | 2024-01-17 | $11,200 | APAC | Net 45 |
| Zephyr Metrics | 2023-12-05 | $7,400 | NA | Net 30 |
The Challenge
The finance team needs to calculate effective monthly cash outflow by applying region-specific VAT rates, adjusting for early-payment discounts (e.g., 2% if paid within 10 days), and flagging contracts expiring within 90 days — all using formulas that must survive copy-paste into Sheets for vendor self-service portals.
That means relying on IFS(), EDATE(), TEXTJOIN(), LET(), and SEQUENCE(). Not exotic stuff — just core modern Excel functionality introduced between 2019–2022.
The trap? Sheets displays #NAME? for missing functions — but only after you’ve already built 12 dependent columns downstream. And LET()? It doesn’t error — it just returns 0. Silent failure.
Walking Through It
Let’s say we want column F to show ‘Discount Eligible?’ based on whether today’s date falls within 10 days of the Contract Start date. In Excel (A2:A10 holds dates), you’d write:
=IF(TODAY()-A2<=10,"YES","NO")
That works in both. But now try adding conditional logic for EMEA vs APAC vs NA tax rules — using nested IFS with array constants. In Excel, this lives in B2:
=IFS(C2>15000,"Tier 1",C2>8000,"Tier 2","Tier 3")
In Sheets? Works fine. Now add TEXTJOIN() in column G to concatenate Region + Payment Terms + Base Rate formatted as currency:
=TEXTJOIN(" | ",TRUE,D2,E2,TEXT(C2,"$#,##0"))
This fails in Sheets with #ERROR!. Why? Because Sheets’ TEXTJOIN doesn’t accept TRUE as the second argument — it demands 1 or 0. A tiny syntax divergence with big ripple effects.
Here’s the before/after for rows 1–5 after fixing that:
| Row | Before (Sheets) | After (Fixed) |
|---|---|---|
| 2 | #ERROR! | EMEA | Net 45 | $12,850 |
| 3 | #ERROR! | APAC | Net 30 | $8,200 |
| 4 | #ERROR! | NA | Net 60 | $21,400 |
| 5 | #ERROR! | EMEA | Net 30 | $6,950 |
| 6 | #ERROR! | APAC | Net 45 | $15,300 |
Now try LET() to avoid repeating TODAY()-A2 three times in one formula. Excel: =LET(diff,TODAY()-A2,IF(diff<=10,"YES",IF(diff<=30,"SOON","NO"))). Sheets? Returns 0 — no error, no warning. You won’t catch it until your forecast shows $0 for all vendors.
The Result
After manual replacement of 7 unsupported functions and 3 syntax tweaks, here’s the clean output — verified in both platforms:
| Supplier | Discount Eligible? | Tax Tier | Formatted ID | Expires Soon? |
|---|---|---|---|---|
| NexaLogix Inc. | YES | Tier 1 | EMEA | Net 45 | $12,850 | NO |
| TerraFiber Ltd. | SOON | Tier 2 | APAC | Net 30 | $8,200 | YES |
| VistaCore Systems | NO | Tier 1 | NA | Net 60 | $21,400 | NO |
| Orion Dynamics | YES | Tier 2 | EMEA | Net 30 | $6,950 | NO |
| Kairos Solutions | SOON | Tier 1 | APAC | Net 45 | $15,300 | YES |
| StrataLink Corp | YES | Tier 2 | NA | Net 30 | $9,700 | NO |
| Helix Data Group | NO | Tier 1 | EMEA | Net 60 | $18,600 | YES |
| Aurora Procure | SOON | Tier 1 | APAC | Net 45 | $11,200 | NO |
| Zephyr Metrics | NO | Tier 2 | NA | Net 30 | $7,400 | NO |
What Could Go Wrong
Here are the three most dangerous missteps — not theoretical, but observed in live audits:
- Mistake #1: Using
EDATE(A2,12)to project renewal date. Excel returns 2025-02-11. Sheets returns#VALUE!— but only if A2 is formatted as Date. If it’s text (e.g., "2024-02-11" entered manually), Sheets quietly returns 45332 — the raw serial number. You’ll see a date like "1900-01-01" instead of an error. - Mistake #2: Copying a range with
SEQUENCE(5)down column H. Excel spills 5 rows. Sheets treats it as a static array and pastes1into every cell — no spill, no warning, no indication it failed. - Mistake #3: Assuming
FILTER()behaves the same. In Excel,=FILTER(A2:E10,C2:C10>10000)returns 5 rows. In Sheets, it returns#N/Aunless you wrap it inARRAYFORMULA()— and even then, headers don’t auto-shift. You get data misaligned by one row.
Pro tip: Press Alt + E + S + V in Excel to paste values only — bypassing formula transfer entirely when moving to Sheets. It’s faster than debugging silent failures later.
So — does Google Sheets have all Excel functions? Here’s the real answer:
| Function | Excel | Sheets | Workaround? | Risk Level |
|---|---|---|---|---|
LET() | ✓ | ✗ (returns 0) | No | Critical |
TEXTJOIN() | ✓ | ✓ (but TRUE→1) | Yes | Medium |
EDATE() | ✓ | ✓ (with date formatting) | Yes | Low |
FILTER() | ✓ | ✓ (requires ARRAYFORMULA) | Yes | Medium |
SEQUENCE() | ✓ | ✗ (no dynamic spill) | No | High |
XMATCH() | ✓ | ✗ | Yes (MATCH() + INDEX()) | Medium |
REDUCE() | ✓ | ✗ | No | Critical |
