9 Google Sheets Functions Every Operations Team Should Memorize

By Swiftools Team · Published December 12, 2025 · 9 min read

Spreadsheets are the duct tape of operations work. Every team I've worked with - and ours included - eventually runs critical processes out of a Google Sheet: vendor lists, onboarding trackers, weekly KPI rollups, on-call rotations. The teams that get the most out of those sheets aren't the ones that know every obscure function. They're the ones that know the same 8-10 functions cold and reach for them by reflex.

This post is that short list. If you internalize these nine, you'll handle 80% of real operations work without copying formulas from Stack Overflow.

1. VLOOKUP and its better sibling, XLOOKUP

VLOOKUP is the function everyone learns first and then keeps using long after they should have switched. The classic pattern looks like this:

=VLOOKUP(A2, vendors!A:D, 4, FALSE)

That says: take the value in A2, find it in column A of the vendors sheet, and return whatever's in the 4th column of that row. The trailing FALSE means "exact match only" - you almost always want that.

The problem with VLOOKUP is that it breaks the moment someone inserts a column in the vendors sheet, because the "4" is now pointing at the wrong column. XLOOKUP fixes that by letting you reference columns by range instead of by number:

=XLOOKUP(A2, vendors!A:A, vendors!D:D)

It's also faster on large sheets and lets you return a default value if nothing matches. If you only learn one new function from this list, make it XLOOKUP. Google's official reference covers the optional arguments.

2. INDEX/MATCH for the situations XLOOKUP can't handle

Before XLOOKUP existed, the standard "do better than VLOOKUP" pattern was INDEX(MATCH()):

=INDEX(vendors!D:D, MATCH(A2, vendors!A:A, 0))

It does the same thing as XLOOKUP, just more verbose. Why bother learning it? Two reasons. First, you'll inherit spreadsheets that use it - other people's tabs are full of INDEX/MATCH and you need to read them. Second, the two functions are independently useful: MATCH alone tells you the row number of a match, which is handy when you want to check whether a value exists without returning data.

3. QUERY when you need real database logic

QUERY is the most powerful function in Google Sheets and the most underused. It lets you run a SQL-like select on a range:

=QUERY(orders!A:F, "select B, sum(D) where C = 'Paid' group by B order by sum(D) desc", 1)

That single cell produces a sorted summary of paid revenue per customer. There's no equivalent that's nearly as compact in Excel. The trailing 1 tells Sheets there's one header row.

The syntax takes a few hours to get comfortable with - it's not standard SQL, it's Google's Visualization API Query Language - but once you do, you'll stop writing dozens of helper columns just to produce a single rollup.

4. SUMIFS and COUNTIFS for any "where" question

Any time someone asks "how many X did we have where Y was Z," reach for COUNTIFS. Any time it's "how much," reach for SUMIFS. The pattern is verbose but predictable:

=SUMIFS(orders!D:D, orders!C:C, "Paid", orders!E:E, ">=2025-11-01")

That reads: sum column D where column C equals "Paid" AND column E is on or after Nov 1, 2025. You can keep adding criteria pairs forever.

Two gotchas: the criteria range and sum range have to be the same length (use full column references like D:D to avoid this), and date comparisons need the quoted operator syntax shown above. If you forget the quotes, you'll get either zero or a #VALUE error with no helpful message.

5. ARRAYFORMULA to stop dragging formulas down

The classic spreadsheet bug is: someone added rows 1,000-1,200 to a sheet, but the formula in column E only goes down to row 999, so the new rows are silently empty. ARRAYFORMULA eliminates that entire failure mode:

=ARRAYFORMULA(IF(A2:A="", "", B2:B * C2:C))

One formula in row 2, and it auto-extends to every row that has data in column A. The IF wrapper prevents stray zeros in empty rows. Most simple Sheets functions (arithmetic, text functions, comparisons) work inside ARRAYFORMULA. A few don't - notably the older lookup functions - which is one more reason to prefer XLOOKUP.

6. IFERROR to make spreadsheets safe to share

You'll inherit or build sheets where a single missing value produces a wall of #N/A errors that scare the people who have to read the sheet. Wrap any formula that can fail in IFERROR:

=IFERROR(XLOOKUP(A2, vendors!A:A, vendors!D:D), "Not found")

For operational dashboards, "Not found" or just "" is almost always better than a red error symbol. The exception is when you're using a sheet for QA - in that case, an error is signal you want.

7. TEXTSPLIT, TEXTJOIN, and the cleanup trio

Real-world data arrives messy. The three text functions that handle most of the cleaning:

• TEXTSPLIT(A2, ",") turns "apple, banana, cherry" into three cells. Useful for unpacking CSV-ish columns from form responses or imports.
• TEXTJOIN(", ", TRUE, A2:A10) does the reverse - takes a column and joins it into one cell with a delimiter, skipping blanks.
• TRIM(CLEAN(A2)) strips leading/trailing whitespace and non-printable characters. Run this on any column you've copy-pasted from a PDF or email before doing comparisons. The number of broken lookups caused by trailing spaces is staggering.

8. UNIQUE and SORT for instant deduplication

To get a deduplicated list of values from a column, the entire formula is:

=SORT(UNIQUE(A2:A))

That gives you a clean, sorted list of distinct values. Combine with COUNTIF in the adjacent column to get a frequency table:

=ARRAYFORMULA(COUNTIF(A2:A, SORT(UNIQUE(A2:A))))

This is the spreadsheet equivalent of GROUP BY ... COUNT(*) and it takes about three seconds to write.

9. IMPORTRANGE to stop emailing copies of sheets

IMPORTRANGE pulls live data from one Google Sheet into another:

=IMPORTRANGE("https://docs.google.com/spreadsheets/d/1abc.../edit", "Sheet1!A:F")

The first time you use it on a new source sheet, you have to authorize the connection by clicking a prompt in the destination sheet. After that, the data flows automatically.

This single function solves the "which version of the spreadsheet is the source of truth" problem. Keep the data in one canonical sheet and import the slices you need into every dashboard or report. You'll never again have three people emailing each other slightly different copies of the same report.

The function I deliberately left off this list

You'll notice GOOGLEFINANCE, IMPORTHTML, and the various web-import functions aren't here. They're cool demos but I've seen them break too many production sheets when the upstream data source changes its layout. If you depend on external data, use Apps Script or a real ETL tool, not a string-parsing formula.

Where to go next

If you're cleaning operational data that arrives via Google Forms, our guide to verifying emails in Google Sheets covers the most common quality check. And if your sheet is being updated by form submissions, see how to wire Google Forms into n8n for processing each new row automatically.

Sources & Further Reading

• Google Sheets function list (official reference)
• Google Visualization API Query Language (the dialect QUERY uses)
• IMPORTRANGE official documentation
• XLOOKUP function reference

Frequently asked questions

Should I use VLOOKUP or XLOOKUP?

XLOOKUP for anything new. It's faster on large sheets, supports a default value when no match is found, and doesn't break when someone inserts a column in the source range. Keep VLOOKUP knowledge only for reading legacy sheets.

What's the difference between QUERY and pivot tables?

Pivot tables are interactive and live in a fixed location on the sheet. QUERY is a single formula that produces a live result anywhere, can be embedded inside other formulas, and supports SQL-like joins and ordering. For dashboards that need to update automatically, QUERY usually wins.

Why does my SUMIFS return zero when I expect a value?

Three common causes: criteria range and sum range have different lengths, date comparisons missing the quoted operator (use ">=2025-01-01" not >=2025-01-01), or trailing whitespace in the text being matched. TRIM(CLEAN(A2)) on the source column fixes the last one.

Does ARRAYFORMULA work with every function?

Most arithmetic, text and comparison functions work inside ARRAYFORMULA. Older lookup functions (VLOOKUP, INDEX/MATCH) need extra wrapping and often misbehave. XLOOKUP, IF, and SUMIFS work cleanly - one more reason to prefer modern functions.

Is IMPORTRANGE safe to use across organizations?

IMPORTRANGE respects Google Sheets sharing permissions - the destination sheet's editor must have at least view access to the source. If the source is shared with the user, the data flows; if not, you get a permission error. Treat it like any other read-only link.

What's the row limit before sheets get slow?

In practice, performance starts degrading around 50k rows of formulas (not raw data - raw data scales further). At that point you want to either convert formulas to values, push the heavy work into a single QUERY or pivot, or move the data into BigQuery and use Connected Sheets.