Lookups and Filters: The Next Step

You can add, average, and count. You can write a basic IF. That covers a single row of data at a time — but most real spreadsheets are about connecting two tables, or pulling a slice out of a much bigger one. That's what this guide is for.

Why SUM and IF aren't enough

A budget with one category is easy. A budget with a "products" sheet and a separate "orders" sheet, where you need to pull the product name and price into the orders sheet, is a different problem. You need a formula that says: find this value somewhere else, and bring back the data next to it. That's a lookup.

XLOOKUP: the modern lookup

XLOOKUP takes three essential arguments: what to search for, where to search, and what to return.

=XLOOKUP(A2, Products!A:A, Products!B:B)

This says: take the value in A2, find it in column A of the Products sheet, and return the matching value from column B.

XLOOKUP defaults to an exact match, searches in any direction (left-to-right or right-to-left, unlike its predecessor VLOOKUP), and takes an optional fourth argument for what to show if nothing is found: =XLOOKUP(A2, Products!A:A, Products!B:B, "Not found").

VLOOKUP and INDEX/MATCH: what you'll still run into

Older spreadsheets — and plenty of new ones — use VLOOKUP instead. It works, but the column to return from is counted by position, not name:

=VLOOKUP(A2, Products!A:B, 2, FALSE)

This means "find A2 in the first column of Products!A:B, then return the value 2 columns over." That 2 breaks silently if someone inserts a column. The other classic pattern, INDEX combined with MATCH, avoids that by separating "where" from "what":

=INDEX(Products!B:B, MATCH(A2, Products!A:A, 0))

You don't need to memorize all three — XLOOKUP covers what VLOOKUP and INDEX/MATCH do, with a simpler syntax. But you will see VLOOKUP and INDEX/MATCH in other people's spreadsheets, so it's worth recognizing the pattern.

FILTER: pulling out a subset of rows

FILTER returns every row that matches a condition, as a live range — not a single cell.

=FILTER(A2:C20, C2:C20 = "Food")

Change the source data and the filtered result updates automatically — no re-running, no manual re-selection. You can stack conditions with * (and) or + (or): =FILTER(A2:C20, C2:C20="Food", B2:B20>20) returns only Food rows over 20.

QUERY: SQL-style power for bigger datasets

QUERY is the function to reach for once FILTER starts feeling limited — sorting, grouping, and aggregating in one formula, using a small SQL-like language:

=QUERY(A2:C20, "SELECT C, SUM(B) WHERE C IS NOT NULL GROUP BY C ORDER BY SUM(B) DESC")

That one line groups every expense by category, sums each group, and sorts from highest to lowest — the equivalent of a small pivot table in a single formula. The syntax takes practice, but it scales to datasets where FILTER and SUMIF become unwieldy.

Putting it together: a two-sheet lookup project

1. Build a Products sheet: column A = SKU, column B = name, column C = price.
2. Build an Orders sheet: column A = SKU ordered, column B = quantity.
3. In Orders!C2, pull the price with =XLOOKUP(A2, Products!A:A, Products!C:C).
4. In Orders!D2, calculate the line total: =B2*C2.
5. In a summary cell, total everything with =SUM(D:D), or break it down by product using =FILTER(A2:D20, A2:A20="SKU-104").

That's the core pattern behind most real reporting spreadsheets: one sheet holds reference data, another sheet looks it up and does the math.

Where to go next

• Once QUERY feels natural, SPARKLINE and conditional formatting turn these numbers into a readable dashboard — covered in the conditional formatting guide.
• For syncing data across separate spreadsheet files instead of separate sheets in one file, look at IMPORTRANGE.
• Read the XLOOKUP vs INDEX/MATCH comparison for a deeper dive on when each is the better choice.

Related functions

• XLOOKUP
• VLOOKUP
• INDEX
• MATCH
• FILTER
• QUERY