Which Google Sheets formulas are worth learning first?
The formulas worth learning first are the ones that join two tables, pull out a subset of rows, summarise data and tidy text, because those four jobs cover most of the manual spreadsheet work in a typical office.
Picture a fictional 15-person events agency, Northgate Events. The operations coordinator, Leila, keeps a bookings sheet and a separate venue price list. Each Friday she copies prices across by hand, filters next month's events, totals revenue by venue and fixes client names typed with stray spaces. The job takes most of the afternoon, and last month a wrong price went on an invoice.
| Client (as typed) | Venue code | Event date | Guests | Price |
|---|---|---|---|---|
| Okafor, Grace | VEN-04 | 14 Nov 2026 | 80 | copied by hand |
| Brandt, Lukas | VEN-11 | 21 Nov 2026 | 35 | copied by hand |
| Mehta, Arjun | VEN-02 | 5 Dec 2026 | 120 | missing |
How do lookup formulas work in Google Sheets?
Lookup formulas find a value in one table and return the matching value from another, which is how Leila can fill every price automatically from the venue list.
XLOOKUP takes three main arguments: the value to find, the single column to search in, and the column to return a value from. Optional arguments set what to show when nothing matches, the match mode and the search order. Exact match is the default, which is what you want for codes like 'VEN-04'. If you leave out the missing value argument and there is no match, it shows the #N/A error.
The older VLOOKUP searches the first column of a range and returns a value from a column you number, counting from 1. Its last argument, is_sorted, defaults to approximate matching, so Google recommends setting it to FALSE for an exact match. Forgetting this is a classic cause of quietly wrong prices.
When should you use FILTER or QUERY?
Use FILTER when you want the rows that meet one or more conditions, and QUERY when you want to select, group or total data in one step.
FILTER takes the range you want and then one or more conditions, each a column of true or false values the same length as the range. Leila can show only events whose date falls in next month. If no rows match, FILTER returns #N/A, which you can replace with a friendly message by wrapping the formula in IFERROR, whose second argument is the value to show when there is an error.
QUERY takes a range, a query written in the Google Visualization API Query Language, and optionally the number of header rows. One QUERY can total revenue by venue for the month. Each column should hold one type of data; in a mixed column the majority type wins and the other values are treated as empty.
What do ARRAYFORMULA and the text functions do?
ARRAYFORMULA lets one formula return results across many rows, so a single formula in the top cell of the price column can fill the whole column, and new bookings are covered automatically. Many formulas that return several values already spill into neighbouring cells without it.
For text, TRIM removes leading, trailing and repeated spaces, leaving one space between words, though it does not remove non-breaking spaces copied from web pages. SPLIT divides text around a delimiter into separate cells, so 'Okafor, Grace' can become surname and first name. TEXTJOIN does the reverse, joining text or a whole range with a delimiter of your choice and, if you ask it to, skipping empty cells, which is handy for building a list of venues for an email.
How do you use Gemini or another AI assistant to write formulas?
Describe the result you want, the exact column letters or headers and one example of the expected answer, then ask the assistant to explain the formula it gives you so you can check it. In Google Sheets, Gemini can generate a formula from a description in its side panel, and on a cell showing an error you can choose Fix to have it review the formula. It needs an eligible Google Workspace or Google AI plan, and Google warns that its suggestions may be inaccurate, so review before relying on them.
With ChatGPT, Claude or another approved assistant, describe the layout instead of pasting client data, for example which tab and column hold the venue codes and which hold the prices. That is enough to draft a formula without sharing anyone's details.
What does a weak and an improved formula request look like?
Weak request: 'Write a formula to get the price.' A typical answer is a VLOOKUP with no is_sorted argument and guessed column numbers. Because approximate matching finds the nearest code, it returns a price even for 'VEN-02', which is not on the venue list.
Improved request: 'In Google Sheets, Bookings column B from row 2 holds venue codes like VEN-04. Venues tab column A holds codes and column C holds prices. Write one formula for Bookings column E that fills the price for every row with an exact match, shows Not on price list when there is no match and stays blank for empty rows. Explain each argument.' The answer uses XLOOKUP with a missing value, wrapped in ARRAYFORMULA with a check for empty rows, and 'VEN-02' now shows Not on price list.
How do you check a formula before you trust it?
Check a formula by testing it on three rows whose answers you already know: one normal, one with no match and one with extra spaces. Confirm exact matching for codes, look for unexpected #N/A or blank results, and compare the assistant's explanation of each argument with the Google Docs Editors Help page for that function.
What are the common mistakes with Google Sheets formulas?
The most common mistake is VLOOKUP without FALSE as its last argument, which returns approximate matches. Others: lookups failing because of trailing spaces, columns that mix numbers and text so QUERY drops values, and hiding every error with IFERROR so genuine problems disappear.
Where does this lesson sit in Tosheo.ai?
This lesson is part of the Automation and Workplace Tools category in the AI automation course. In Tosheo.ai the hands-on task gives you a fictional messy sheet to fix with formulas drafted by an AI assistant, and the rubric feedback checks exact matching, error handling and your test rows.
Sources and scope
Google Docs Editors Help: XLOOKUP function (checked 19 October 2026) ↗
Google Docs Editors Help: VLOOKUP function (checked 19 October 2026) ↗
Google Docs Editors Help: FILTER function (checked 19 October 2026) ↗
Google Docs Editors Help: QUERY function (checked 19 October 2026) ↗
Google Docs Editors Help: ARRAYFORMULA function (checked 19 October 2026) ↗
Google Docs Editors Help: IFERROR function (checked 19 October 2026) ↗
Google Docs Editors Help: TRIM function (checked 19 October 2026) ↗
Google Docs Editors Help: SPLIT function (checked 19 October 2026) ↗
Google Docs Editors Help: TEXTJOIN function (checked 19 October 2026) ↗
Google Docs Editors Help: collaborate with Gemini in Google Sheets (checked 19 October 2026) ↗