Lookup and rollup fields are two of the most useful — and most confused — field types in Airtable. They both work with linked records. They both surface data from related tables. But they do fundamentally different things, and using the wrong one creates subtle problems that are annoying to debug.
Here's the clear breakdown.
Try Airtable and put lookups and rollups to work in your next base.
Disclosure: some links in this post are affiliate links. If you sign up through them, GAP Consulting earns a small commission at no extra cost to you.
The clearest way to think about the difference:
Lookup = a window. It shows you a value from a linked record. You're looking through the link into the related table and seeing what's there. The value doesn't change — you're just displaying it.
Rollup = a calculator. It takes values from multiple linked records and performs a calculation: sum, count, average, min, max, concatenate. The result is computed from the linked records, not just displayed.
If you need to see a value from one linked record, use a lookup. If you need to aggregate values across multiple linked records, use a rollup.
Lookup fields make sense when:
Lookup fields can return multiple values if a record links to multiple records — you'd see a comma-separated list. For single-link relationships (one project to one client), the lookup returns a clean single value.
Rollup fields make sense when:
Rollup fields require an aggregation function. You're not displaying a value — you're computing one from a set of values.
Using a lookup when you need a rollup: You add a lookup field to show "total hours" from linked tasks, but it returns a comma-separated list of each task's hours instead of a sum. That's a lookup — it shows the values. Use a rollup with SUM to get the total.
Using a rollup when you need a lookup: You add a rollup with CONCATENATE to display a client's name from a linked record. This works, but it's unnecessary complexity. A lookup field returns the value directly without an aggregation step.
Not having a linked record field first: Both lookups and rollups require an existing linked record field in the same table. If there's no link, there's nothing to look up or roll up.
Well-placed lookups and rollups make reporting significantly easier. A project table with rollup fields for total budget, total hours logged, and count of open tasks gives you a summary dashboard without building a separate report.
The key: design which rollup values you need at each level of your hierarchy (project, client, portfolio) before you start building. Adding them retroactively works, but planning ahead keeps the schema cleaner.
Build on Airtable and use lookups and rollups to surface the right data at every level of your base.