Skip to content

No account required

Start a project

Google Sheets formula generator

Describe what you want the sheet to do. You get the formula, and the reason it is written that way.

Plain English. The way you would explain it to a colleague.

Optional, and the single biggest difference between a usable answer and a generic one.

A worked example

“Look up each order's product in the Products tab and bring back the price”

=ARRAYFORMULA(IF(B2:B="","",IFERROR(VLOOKUP(B2:B,Products!A:C,3,FALSE),"Not found")))

ARRAYFORMULA runs the lookup for every row at once, so this one cell fills the whole column and keeps working when new orders arrive. The IF skips rows where there is no product yet, and IFERROR turns a failed lookup into a readable label instead of #N/A.

What it assumes

  • Writes "Not found" where a product is not in the Products tab. Change that text if you would rather see the raw #N/A and catch it yourself.
  • VLOOKUP takes the first match. If a product appears twice in the Products tab with different prices, the second one is silently ignored.
  • Put this in row 2 only. Copying it down produces overlapping arrays and a #REF error.

01

It uses the functions Sheets actually has.

ARRAYFORMULA, QUERY and REGEXMATCH have no Excel equivalent and are often the shortest correct answer. It will not hand you XLOOKUP, which Sheets does not have.

02

One formula instead of a filled-down column.

Where a whole column can be computed by a single ARRAYFORMULA in row 2, that is what you get. It survives new rows being added, which a dragged-down formula does not.

03

It says where it breaks.

What happens to blank rows, what a lookup returns when there is no match, and whether the formula will slow the sheet down at ten thousand rows.

Questions people ask

Will it give me Excel formulas by mistake?

No. The target is set per page, so this one is told it is writing for Google Sheets and that Excel-only functions such as XLOOKUP and LET are unavailable to it.

What is ARRAYFORMULA and why does it keep appearing?

It applies a formula to a whole column from one cell. It is worth the extra typing because a sheet other people add rows to will break a dragged-down column and will not break an ARRAYFORMULA.

My sheet is in a locale that uses semicolons. Will this work?

The formulas are written with commas, which is what the default locale expects. If your Sheets locale uses semicolons as argument separators, swap the commas for semicolons and it behaves identically.

Do I need to share my sheet with anyone?

No. Nothing is connected and nothing is uploaded. You describe the job in words, and the formula comes back as text to paste in yourself.

When it is a whole file

A formula fixes one column. If what you actually have is four exports and a question, drop them here and you get the cleaned data, the analysis and the charts back.

No account needed to start. You only pay when you like what you see.