Quick answer: Master about 20 Google Sheets formulas — VLOOKUP, XLOOKUP, QUERY, SPLIT, SUMIFS, and a handful more — and you'll handle 95% of real spreadsheet work. This guide groups each formula by the job it does, gives you a copy-paste example for every one, and flags the mistakes that waste the most time.
Most spreadsheets run on the same dozen formulas repeated a few thousand times. Learn that dozen properly and the sheet does the heavy lifting while you read the results — no add-ons, no scripts, no paid plan required. Everything below works in the free personal version of Google Sheets, and every example pastes straight into a blank cell.
The 20 formulas are grouped by the job they do: lookups, text cleanup, dates, and logic plus math, with one finance bonus at the end. If you only skim, the reference table and the mistakes list near the end are the two sections worth stealing. And because formulas are only half the stack, the picks in our best productivity apps guide cover the tools that sit around the spreadsheet.
Lookup Formulas: Find Anything in Seconds
These four answer the daily question, "where else does this value live?" Start with VLOOKUP, then graduate upward.
1. VLOOKUP — =VLOOKUP(A2, Inventory!A:D, 4, FALSE) searches the first column of the Inventory range for the value in A2 and returns whatever sits in the fourth column of that row. The final FALSE forces an exact match; leave it off and VLOOKUP quietly returns wrong rows.
2. XLOOKUP — =XLOOKUP(A2, Inventory!A:A, Inventory!D:D, "Not found") is the modern upgrade: search any column, return any column, look left or right, and show a friendly message instead of #N/A when nothing matches.
3. INDEX and MATCH — =INDEX(Inventory!D:D, MATCH(A2, Inventory!A:A, 0)) is the classic power combo. MATCH finds which row holds A2; INDEX returns the value from column D in that row. It is more flexible than VLOOKUP and still the fastest option on very large sheets.
4. QUERY — =QUERY(A1:D500, "select B, D where D > 50 order by D desc", 1) runs a SQL-style statement across your data: filter rows, pick columns, sort, even aggregate, all from one cell. If you learn one formula after the basics, make it this one.
Text Formulas: Clean Up Messy Data
Imported CSVs and pasted exports are never clean. These four do the janitor work.
5. SPLIT — =SPLIT(A2, ",") breaks one cell into separate columns at every comma. It is the fastest way to dismantle "Last, First" name fields, address strings, or semicolon-joined lists.
6. TEXTJOIN — =TEXTJOIN(", ", TRUE, A2:A10) glues a range back into one cell with a delimiter of your choice, and TRUE skips blanks so you never get double commas.
7. REGEXEXTRACT — =REGEXEXTRACT(A2, "\d{5}") pulls the first five-digit run out of messy text — ideal for ZIP codes, order numbers, or SKU fragments buried in free-form notes.
8. TRIM — =TRIM(A2) strips leading, trailing, and doubled spaces. It sounds trivial, but invisible whitespace is the number-one reason lookups fail for no visible reason.
Date Formulas: Deadlines Without the Calendar Math
9. TODAY — =TODAY() inserts today's date and refreshes itself every day. Combine it with plain arithmetic, like =B2-TODAY(), to show days remaining on any deadline.
10. EDATE — =EDATE(A2, 3) returns the same day three months later. It is built for renewal dates, subscription billing, and any "same day next quarter" schedule.
11. NETWORKDAYS — =NETWORKDAYS(A2, B2) counts working days between two dates, skipping weekends. Add a third argument pointing at a holidays list and it skips those days too.
12. DATEDIF — =DATEDIF(A2, B2, "M") returns whole months between two dates; swap "M" for "Y" or "D" as needed. Google never added it to autocomplete, which is why most people never find it.
Logic and Math Formulas: Summarize Anything
13. IF — =IF(B2>=1000, "Bonus", "Standard") returns one value when a test is true and another when it is false. Every dashboard you will ever build starts here.
14. IFS — =IFS(B2>=1000, "A", B2>=500, "B", TRUE, "C") chains several tests without nesting IF inside IF inside IF. The final TRUE works as the fallback tier.
15. SUMIF — =SUMIF(A:A, "West", B:B) adds column B only on rows where column A says "West". One condition, no pivot table required.
16. SUMIFS — =SUMIFS(B:B, A:A, "West", C:C, ">100") takes several conditions at once: West region and sales above 100. Notice the operator travels inside the quotes.
17. COUNTIF — =COUNTIF(A:A, "*@gmail.com") counts rows matching a pattern, wildcards included. Swap the pattern for a plain word to count exact matches.
18. UNIQUE — =UNIQUE(A2:A500) returns each distinct value exactly once. It is the fastest dedupe in any spreadsheet, and its output makes a perfect source for drop-downs and summary blocks.
19. FILTER — =FILTER(A2:D500, D2:D500>50) returns only rows that meet a condition, spilling them below the formula. A note for Workspace users: managed domains on the Scheduled Release track receive brand-new functions a few weeks after personal accounts, so an unrecognized-formula error at work usually means the rollout has not reached your domain yet, not that you typed anything wrong.
20. GOOGLEFINANCE — =GOOGLEFINANCE("NASDAQ:AAPL", "price") pulls live stock prices, historical series, and currency rates straight into the grid. It is the party trick that converts spreadsheet skeptics fastest.
The 20 Google Sheets Formulas at a Glance
Here is the quick-reference version: the formulas people actually reach for weekly, what each one does, and a realistic use case.
| Formula | What it does | Example use case |
|---|---|---|
| VLOOKUP | Matches the first column of a table and returns a value from another column | Pull a price from a 5,000-row price list |
| XLOOKUP | Searches any column, returns any column, with a built-in "not found" message | Match order IDs when the return column sits to the left |
| QUERY | Filters, sorts, and summarizes with SQL-style commands | Weekly report of orders over $500, newest first |
| SPLIT | Breaks one cell into several columns at a delimiter | Split "Last, First" into two clean columns |
| TEXTJOIN | Combines a range into one string, skipping blanks | Build one mailing line from scattered cells |
| TRIM | Removes stray spaces that silently break matching | Repair a pasted export before running lookups |
| TODAY | Today's date, always current | A days-until-deadline column |
| EDATE | Shifts a date by whole months | Next renewal date for annual contracts |
| NETWORKDAYS | Counts weekdays between two dates | Billable-day counts that skip weekends |
| SUMIFS | Sums rows matching multiple conditions | Total West-region sales above $100 |
| UNIQUE | Lists each distinct value once | A clean category list for a summary block |
| GOOGLEFINANCE | Live market, historical, and currency data | A portfolio tab that prices itself |
5 Common Mistakes (and the Fix)
- Dropping FALSE from VLOOKUP. Without it, the formula switches to approximate matching and can silently return the wrong row. Almost every lookup you write wants FALSE.
- Forgetting the dollar signs. Lock ranges like $D$2:$D$50 before dragging a formula down a column, or the range slides with the fill handle and starts matching nothing.
- Invisible spaces. "Jane " never equals "Jane". Wrap both sides of a comparison in TRIM when matching data from two different sources.
- Numbers stored as text. Left-aligned numbers and dates refuse to add up or match. Force them back with VALUE for numbers, or re-enter dates after checking the column format.
- Expecting VLOOKUP to look left. It cannot return a value from a column before the search column. Switch to XLOOKUP or INDEX with MATCH and the limitation disappears.
Every function above is documented with syntax and worked examples in Google's official Sheets function list, which is the only reference worth bookmarking — it always reflects the current version.
The Bottom Line
Learn the four lookups first — VLOOKUP, XLOOKUP, INDEX with MATCH, and QUERY — because they unlock everything else on this page. Then add SUMIFS, UNIQUE, and FILTER for summaries, and you will cover virtually every personal or small-business sheet you will ever build. If your planning lives outside the grid, mirroring those summaries into Notion works well; our guide on how to use Notion walks through the setup.
When a task outgrows the grid, automation and assistants now draft formulas and clean data on request — the picks in our best AI tools guide are the honest shortlist. And if your team runs on Excel instead, read our Microsoft 365 Family vs Personal comparison before paying for a plan you do not need.



