Google Sheets: QUERY, ARRAYFORMULA and LAMBDA without the fear

If you've got 20 separate sheets doing the work that three formulas could handle better, you've got a maintenance problem. Change a number in the source and everything breaks. Update one sheet and forget another. Two months later, your boss asks why the figures don't add up.

This is chapter 3 of the Google Sheets series. After looking at practical day-to-day tricks and questions everyone asks, it's time for the formulas that change the game: QUERY, ARRAYFORMULA and LAMBDA.

You don't need Apps Script. You don't need pivot tables. You don't need manual references. Three formulas. Thousands of possibilities.

Let's go with examples you copy, paste and they work within a minute.

QUERY: Google Sheets' SQL

QUERY is the closest thing to writing SQL inside Sheets. Filter, sort, group and pivot data with one formula.

QUERY syntax

=QUERY(range; "SELECT columns WHERE condition ORDER BY column LIMIT n")

Breakdown:

• range: where your data lives (includes header).
• SELECT: which columns you return. * means all. Or specify them by name or number (A, B, C or Col1, Col2, Col3).
• WHERE: filtering condition. Use operators: =, <>, >, <, >=, <=, AND, OR, LIKE.
• GROUP BY: group by column. Combine with functions: SUM, COUNT, AVG, MAX, MIN.
• ORDER BY: sort by column and direction (ASC, DESC).
• LIMIT: return N rows.
• OFFSET: skip N rows.
• PIVOT: create pivot table.

Example 1: Monthly sales by sales rep

You've got unsorted sales data.

Source data (sheetSales, A1:D100):

Formula: Total sales by sales rep, sorted highest to lowest.

=QUERY(sheetSales!A:D;
  "SELECT B, SUM(D)
   WHERE D IS NOT NULL
   GROUP BY B
   ORDER BY SUM(D) DESC")

Expected result:

Adjust for a specific month:

=QUERY(sheetSales!A:D;
  "SELECT B, SUM(D)
   WHERE MONTH(A) = 1 AND YEAR(A) = 2024
   GROUP BY B
   ORDER BY SUM(D) DESC")

Example 2: Filter and list only sold products

Formula: Unique products sorted alphabetically.

=QUERY(sheetSales!A:D;
  "SELECT C
   WHERE C IS NOT NULL
   ORDER BY C ASC")

No manual changes needed. Add a sale with a new product and it appears automatically.

Example 3: Top 5 transactions

=QUERY(sheetSales!A:D;
  "SELECT A, B, C, D
   WHERE D IS NOT NULL
   ORDER BY D DESC
   LIMIT 5")

Returns the 5 highest amounts.

Example 4: Pivot table with PIVOT

Formula: Sales by sales rep (rows) and product (columns).

=QUERY(sheetSales!A:D;
  "SELECT B, C, SUM(D)
   WHERE D IS NOT NULL
   GROUP BY B, C
   PIVOT C")

Result:

QUERY that breaks

Problem 1: Empty range after filter

If WHERE finds no data, it returns a syntax error. Solution:

=IFERROR(QUERY(...); "No data")

Problem 2: Inconsistent headers

QUERY searches by column name. If your header says "Sales" in the source but you write "Sale" in SELECT, you get an error.

Problem 3: Mixed data types

If column A has numbers as text ("5000" vs. 5000), QUERY won't filter properly. Clean the data first with VALUE() or NUMBERVALUE().

ARRAYFORMULA: apply formulas without dragging

ARRAYFORMULA applies a formula to an entire range at once. No copying down. No calculating 100 times.

Syntax

=ARRAYFORMULA(formula_that_affects_range)

Example 1: Classify customers by channel

Data (A2:C1000):

Formula: Classify as corporate or personal.

=ARRAYFORMULA(
  IF(LEN(A2:A1000)=0; "";
    IF(REGEXMATCH(B2:B1000; "company\.com$"); "Corporate"; "Personal")
  )
)

Place in D2 and done. The entire column D fills in one calculation. Change an email in the source and D updates automatically.

Result:

Example 2: Concatenate with condition

Case: Generate custom URLs if ID exists.

=ARRAYFORMULA(
  IF(LEN(A2:A)=0; "";
    "https://myapp.com/user/" & A2:A
  )
)

Result:

• https://myapp.com/user/1
• https://myapp.com/user/2
• https://myapp.com/user/3

Example 3: Deduplicate and count

Case: List of customers with purchases. You want unique customers plus number of transactions.

Data (A2:B1000):

Formula: Unique customers with sum of purchases.

This combines QUERY better than ARRAYFORMULA:

=QUERY(A2:B1000;
  "SELECT A, SUM(B)
   GROUP BY A
   ORDER BY SUM(B) DESC")

But if you want to use ARRAYFORMULA plus SUMIF:

=ARRAYFORMULA(
  IF(COUNTIF(A$2:A2; A2:A) = 1;
    A2:A & " | " & SUMIF(A2:A; A2:A; B2:B);
    ""))

This marks only the first occurrence of each customer and sums their totals. Other occurrences stay empty.

Example 4: Calculate margin per line

Data (A2:D1000):

Formula:

=ARRAYFORMULA(
  IF(LEN(A2:A)=0; "";
    (B2:B * C2:C) * 0.30
  )
)

Calculates 30% of each line. Change quantity or price and margin updates instantly.

ARRAYFORMULA common mistakes

Error 1: Different range sizes

=ARRAYFORMULA(A2:A + B2:B100)  // WRONG: A2:A is "to the end", B2:B100 is limited

Solution: use the same range end.

=ARRAYFORMULA(A2:A100 + B2:B100)

Error 2: Using functions that aren't "array-friendly"

ARRAYFORMULA doesn't work well with: VLOOKUP (use INDEX/MATCH instead), HYPERLINK, some date functions.

Error 3: Not closing with IF condition

If you leave empty cells without IF, ARRAYFORMULA returns 0 or FALSE. Always wrap:

=ARRAYFORMULA(IF(LEN(A2:A)=0; ""; your_formula))

LAMBDA: reusable custom functions

LAMBDA is the most modern option. It lets you create your own functions without Apps Script. Use them once or a thousand times in the same sheet.

Syntax

=LAMBDA(parameter1; parameter2; ... ; logic)(argument1; argument2; ...)

Or define with a name and reuse:

Named manager > New function
Name: MyFunction
Definition: =LAMBDA(x; y; x + y)

Cell usage: =MyFunction(5; 3)  // Returns 8

Example 1: Convert temperature C to F

Simple LAMBDA:

=LAMBDA(celsius; (celsius * 9/5) + 32)(25)

Returns: 77

With name (reusable):

In Named manager (Tools > Named manager):

Name: CelsiusToFahrenheit Definition: =LAMBDA(C; (C * 9/5) + 32)

Then in any cell:

=CelsiusToFahrenheit(A2)

Fill down to apply to entire column.

Example 2: Conditional discount

Case: If sale > 10,000, discount 15%. If > 5,000, 10%. Otherwise, 0.

LAMBDA with LET for intermediate variables:

=LAMBDA(sale;
  LET(
    discount_high; 0.15;
    discount_mid; 0.10;
    result;
      IF(sale > 10000; sale * (1 - discount_high);
        IF(sale > 5000; sale * (1 - discount_mid);
          sale
        )
      );
    result
  )
)(B2)

LET lets you name intermediate variables. Makes code more readable.

Apply to range:

=ARRAYFORMULA(
  LAMBDA(sale;
    LET(
      disc_high; 0.15;
      disc_mid; 0.10;
      IF(sale > 10000; sale * (1 - disc_high);
        IF(sale > 5000; sale * (1 - disc_mid);
          sale
        )
      )
    )
  )(B2:B1000)
)

Example 3: Calculate age from birth date

Name: CalculateAge Definition:

=LAMBDA(birth_date;
  DATEDIF(birth_date; TODAY(); "Y")
)

Usage:

=CalculateAge(A2)

Returns full years.

Example 4: MAP: apply function to each element

MAP is one level up. It loops through a range and applies a function.

Case: Multiply each cell in A2:A10 by 2.

=MAP(A2:A10; LAMBDA(x; x * 2))

Result:

• A2 = 5 > 10
• A3 = 10 > 20
• Etc.

Advanced case: MAP with two ranges.

Multiply A2:A10 by B2:B10.

=MAP(A2:A10; B2:B10; LAMBDA(a; b; a * b))

Example 5: REDUCE: aggregate values

Case: Sum of all values in A2:A1000.

=REDUCE(0; A2:A1000; LAMBDA(accumulated; value; accumulated + value))

Reduce takes:

1. Initial value (0).
2. Range to process (A2:A1000).
3. LAMBDA with accumulated and current value.

Returns: total sum.

Combinations: mix all three

Pattern 1: QUERY inside LAMBDA

Create a function that filters and sums with a dynamic parameter.

Case: Function that returns sales for a specific sales rep.

=LAMBDA(sales_rep_name;
  QUERY(sheetSales!A:D;
    "SELECT B, SUM(D)
     WHERE B = '" & sales_rep_name & "'
     GROUP BY B")
)(A2)

Place the sales rep name in A2 and the formula returns their total.

Pattern 2: ARRAYFORMULA plus QUERY combined

Case: For each row, look up the sales rep's total from that row.

=ARRAYFORMULA(
  IF(LEN(A2:A)=0; "";
    QUERY(
      {sheetSales!B:B; sheetSales!D:D};
      "SELECT SUM(Col2)
       WHERE Col1 = '" & A2:A & "'"
    )
  )
)

Problem: QUERY doesn't work well inside ARRAYFORMULA on ranges. Better to use VLOOKUP or INDEX/MATCH.

Pattern 3: LAMBDA with MAP to list sales by sales rep

=LAMBDA(salesRepList;
  MAP(salesRepList;
    LAMBDA(salesRep;
      QUERY(sheetSales!A:D;
        "SELECT B, SUM(D)
         WHERE B = '" & salesRep & "'
         GROUP BY B")
    )
  )
)(A2:A10)

This is powerful but slow if you've got many sales reps.

Real case: consolidate monthly KPIs

Imagine you've got three sources: sales, costs and returns.

Goal: single dashboard with consolidated KPIs.

Step 1: Sales table (sheetSales, A1:D1000)

Step 2: Costs table (sheetCosts, A1:C1000)

Step 3: Consolidated dashboard (mainSheet)

Cell A1:

=QUERY(sheetSales!A:D;
  "SELECT A, SUM(D)
   WHERE A IS NOT NULL
   GROUP BY A
   ORDER BY A ASC")

Returns sales by month.

Cell D1:

=QUERY(sheetCosts!A:C;
  "SELECT A, SUM(C)
   WHERE A IS NOT NULL
   GROUP BY A
   ORDER BY A ASC")

Returns costs by month.

Cell G1: Margin (income minus costs).

=ARRAYFORMULA(
  IF(LEN(B2:B)=0; "";
    B2:B - E2:E
  )
)

Result:

Untouched. Everything updates in real-time if you add data.

When to move to Apps Script

QUERY, ARRAYFORMULA and LAMBDA handle 95% of cases. But there's a line:

Use Apps Script if you need:

• Send automated emails based on conditions.
• Query external APIs (Stripe, Pipedrive, etc.).
• Create backups or export to other tools.
• Process images.
• Run complex logic that needs nested loops (though LAMBDA+REDUCE improves each version).

That's chapter 4.

Common mistakes: quick checklist

1. QUERY returns syntax error.

   • Check column names. Use the formula explorer to verify the range.

2. ARRAYFORMULA doesn't expand to the full range.

   • Make sure A2:A (open) doesn't mix with A2:A100 (closed).

3. LAMBDA returns #NAME!

   • Though LAMBDA is standard, some older Sheets instances don't have it. Update.

4. Formula slows down.

   • Nested QUERY inside ARRAYFORMULA is slow. Use direct QUERY or INDEX/MATCH.

5. Circular reference.

   • Don't use QUERY or ARRAYFORMULA on the same column where the formula lives.

Quick summary

Master these three and your data flow improves tenfold. No APIs. No external code. No manual upkeep.

Next steps

If you need help structuring data, consolidating reports or automating Sheets without code, my advisory team can review your current flow.

Book your 30-minute call to diagnose where you can save hours each week.

Or if you prefer hands-on training, check out the Google Sheets courses with step-by-step exercises.

In chapter 4 we'll look at when Apps Script is the real answer.