9 Google Sheets Functions Every Operations Team Should Memorize
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.