Lookup vs rollup: which one do you actually need?
Both read data across a link. A lookup borrows one record's value; a rollup summarises many. Here is how to tell them apart and pick correctly every time.
Once two tables are linked, you get two ways to use that link. People mix them up constantly, then wonder why a field shows a list where they expected a number.
The distinction is simpler than the names suggest.
The one-sentence version
- A lookup borrows a value from the record on the other end of the link.
- A rollup summarises values from many records on the other end.
Lookups are about the "one" side. Rollups are about the "many" side.
Which side am I standing on?
This is the whole trick. Every link has a "one" end and a "many" end.
Take projects and clients: one client has many projects.
- Standing on a project, you are looking at one client. So you use a lookup — pull the client's region, owner, payment terms.
- Standing on a client, you are looking at many projects. So you use a rollup — total their value, count the open ones, find the latest close date.
If you catch yourself trying to lookup from the client to the projects, that is the signal you want a rollup. You cannot borrow "the" project's value when there are eleven of them.
Lookups in practice
A lookup answers: what does the thing I am linked to say?
On a project record:
- Client region — so you can group projects by region without retyping it.
- Client payment terms — so an invoice can be dated correctly.
- Account manager — so you can filter to your own work.
The critical property: a lookup is not a copy. It reads through the link at display time. Change the client's region and every linked project reflects it immediately. There is no sync, because there is nothing to sync — the value only ever lived in one place.
This is what makes lookups strictly better than the VLOOKUP they replace. A VLOOKUP produces a new value in a new cell that can drift, break when columns move, or match the wrong row. A lookup field cannot do any of those things.
When a lookup is the wrong tool
If you need the value to be frozen — the price at the time of order, not the price now — a lookup is wrong. Lookups always show current truth.
For historical accuracy, copy the value into a normal field at the moment it matters. Order line items are the classic case: you want the price as sold, not the price as it is today. More on this in orders and line items.
Rollups in practice
A rollup answers: what do all the things pointing at me add up to?
Every rollup has three parts:
- Which link to follow (Projects).
- Which field on the far side to read (Value).
- Which operation to apply (Sum).
Common operations:
| Operation | Answers |
|---|---|
| Sum | Total invoiced, total stock on hand, total hours |
| Count | How many projects, applications, incidents |
| Average | Mean deal size, average time to close |
| Min / Max | First contact date, latest activity, highest bid |
| And / Or | Are all line items shipped? Is any task blocked? |
| Concatenate | A readable list of linked item names |
The result is a real, filterable, sortable value. You can sort clients by total value, filter to clients with more than five open projects, or chart revenue by region — all without building anything.
Filtered rollups
The genuinely useful version. Rather than counting all projects, count only those where status is Open. Rather than summing all invoices, sum only the unpaid ones.
Most of the numbers a business actually watches are filtered rollups:
- Open pipeline value = sum of deal value where stage is not Won or Lost.
- Overdue invoices = count where due date is in the past and paid is false.
- Stock on hand = sum of movements where type is Receipt, minus where type is Issue.
This single feature replaces most hand-maintained summary tabs.
A worked example
A simple order system: Orders, Line items, Products. Each line item links to one order and one product.
On a line item (the "many" side of both links) — use lookups:
- Product name, via the Product link.
- Unit price, via the Product link — though see the freezing caveat above.
Then a normal formula field: Quantity × Unit price = Line total.
On an order (the "one" side, looking at many line items) — use rollups:
- Item count = Count of Line items.
- Subtotal = Sum of Line items → Line total.
- All shipped? = And of Line items → Shipped.
On a product (the "one" side, looking at many line items) — also rollups:
- Units sold = Sum of Line items → Quantity.
- Revenue = Sum of Line items → Line total.
- Last ordered = Max of Line items → Order date.
Notice the same table, Line items, feeds rollups on two different parents. That is normal and it is the point: one set of facts, summarised from whichever direction you need.
The four mistakes
1. Using a rollup where you meant a lookup. You get a list or a sum where you wanted a single value. Usually means you are standing on the wrong side of the link.
2. Expecting a lookup to freeze. It will not. It always shows current truth. If you need a historical value, store it.
3. Rolling up a rollup without thinking. Legitimate — a client's total from projects' totals from line items — but each hop adds indirection. If a number looks wrong, walk the chain one hop at a time.
4. Rolling up text and expecting arithmetic. Sum over a text field gives nothing useful. Check the far-side field's type first.
The rule of thumb
Ask one question: am I pointing at one thing, or many?
- One thing → lookup. Borrow its value.
- Many things → rollup. Summarise them.
Everything else is detail. Get the direction right and the rest follows.
Where to go next
- Linked records explained — the foundation both of these sit on.
- Rollups, formulas and when to reach for each — the other half of calculated fields.
- Orders and line items — this pattern as a full build.