I grew up on Microsoft Excel. I knew the formulas, the keyboard shortcuts, the quirks. When I eventually moved most of my day-to-day work into Google Sheets, I assumed it was basically the same tool with a collaboration layer bolted on—useful, but not something I needed to study. That held up fine for a while. Over the years, I’ve poked around, and a handful of features stopped me cold. Some I’d written off as too complicated. Others, I genuinely had no idea were there.
INDEX MATCH is what VLOOKUP should have always been
Look up values from any direction
For years, VLOOKUP was my default lookup tool. Column number, range, approximate or exact—I could write it in my sleep. It has a real structural flaw, though: it only searches left-to-right, and your lookup column has to sit at the far left of your selected range. Insert a column anywhere in the middle of your data, and that hardcoded column number goes stale. The formula keeps running.
The results just quietly become wrong. INDEX MATCH sidesteps all of that. INDEX returns the value at a given position in a range; MATCH finds the position of your search value within another range. Nested together, they retrieve data from any direction—left, right, it doesn’t matter. And because you’re referencing full columns rather than counting column numbers, inserting or deleting columns doesn’t break the formula.
It just keeps working. The syntax takes about ten minutes to get comfortable with. Once it clicks, going back to VLOOKUP feels like a downgrade. INDEX MATCH has saved me countless hours matching thousands of data points from separate sheets and tabs in my engineering and marketing career. For anyone still defaulting to VLOOKUP out of habit (both in Sheets and Excel), INDEX MATCH is worth a dedicated afternoon to learn.
Related
This Google Sheets function makes Excel feel like a dinosaur
Google Sheets now has one trick that Excel doesn’t.
The “help me write a formula” button is actually useful
Gemini does the formula syntax work for you
My first instinct was to ignore this entirely. A button that writes formulas for you sounded like it would produce something technically correct but practically useless—close enough to look right, broken enough to waste your time fixing it. After actually using the Gemini sidebar in Sheets, I changed my mind. You describe what you want in plain English, and Sheets writes the formula.
Ask it to total sales in column C only where column B says “North,” and it produces a working SUMIF with your actual cell references already filled in. Google has since added the ability for Gemini to walk through the formula line by line—what each part does, why it’s structured that way—which is useful when the output pulls in functions you don’t reach for regularly. Is your formula throwing an error? Gemini can read the error message, explain what went wrong in plain language, and generate a corrected version.
This pairs well with the LET function, which lets you assign readable names to values inside a formula and use those names in a final calculation—so once Gemini writes you something complex, LET can make it readable enough to actually maintain. Access to the Gemini sidebar requires an eligible Google Workspace or Google AI plan, so it isn’t available on a free account by default.
Pivot tables are hiding more power than the name suggests
Summarize thousands of rows in seconds
Pivot tables get described as either essential or overwhelming, rarely anything in between. I’d used them in Excel and expected Google’s version to cut corners somewhere. It doesn’t. The core workflow is the same—drag fields into rows, columns, and values, and Sheets summarizes your data—but reorganizing on the fly is faster than I expected.
Drag something from rows to columns, switch the aggregation from sum to average, drop in a filter—none of it requires rebuilding from scratch. Calculated fields let you create custom metrics that live inside the pivot table itself, which beats maintaining a separate column of helper formulas elsewhere in the sheet.
Layering in conditional formatting on top of pivot output makes the patterns jump out immediately—high values in green, outliers in red—without having to scan rows manually. If you’ve been doing this work by hand, filtering and copying figures into a separate summary tab, a pivot table handles it in a fraction of the time.
The LET function cleaned up my worst formulas
Name your values, stop repeating yourself
Screenshot by Chifundo Kasiya—No attribution required
Anyone who has inherited a deeply nested spreadsheet formula knows the particular frustration of trying to figure out what it actually does. The worst ones reference the same range repeatedly—$A$2:$A$500, showing up four, five, six times across a single formula. Miss one when you’re updating it, and the results drift with no obvious error to flag it.
LET fixes this at the source. You define named variables directly inside the formula—assign a name like salesData to your range once, then use that name wherever the range is needed. Update it in one place, and every reference in the formula updates with it. It’s also easier to pair LET with error-handling. Wrapping a lookup result in IFNA—rather than the more common IFERROR—gives you more precise control over #N/A errors, specifically without accidentally masking unrelated issues.
Inside a LET structure, that kind of intentional error handling is much more readable than burying IFNA inside an already-complex nested formula. For anyone who uses Excel alongside Sheets, Excel’s Power Query can pull live data directly from the web without any coding—a similar “I didn’t know it could do that” moment hiding in a tool you might already have open.
Sheets rewards the curious—even experienced users
INDEX MATCH, Gemini formula generation, pivot tables, LET—none of these are hidden settings or obscure workarounds. They’re core tools I either skipped because I had a habit that worked well enough, or dismissed because I assumed the learning curve wasn’t worth it. Mostly, I was just defaulting to what I already knew from Excel rather than actually exploring what Sheets could do on its own terms. It turns out there’s more here than I gave it credit for.
