FILTER function — no formulas, no manual table relations. Just set filter conditions to find and reference data, with built-in aggregation.
Add a field
Click + on the right — pick Lookup under field types.Configuration
Field to reference
Pick the field to reference — cross-table referencing is supported. Pick the table and the field within it.Filter conditions
Add multiple filter conditions. The operators (equals, not equals, contains, doesn’t contain, is empty, is not empty) sit between a field from the referenced table (left) and a field from the current table or a literal string / option (right; when the left field is single-select). You can require all conditions to match, or any of them.Aggregation
Pick an aggregation to compute over the matching records, then a number format.| Aggregation | Description | Example use |
|---|---|---|
| Original | Reference column data directly | With a filter, return the original value of records matching a condition |
| Deduplicated | Remove duplicates | Strip duplicate records |
| Sum | Add up the original values | With a filter, see the sum of records matching a condition |
| Average | Average of original values | With a filter, see the average of records matching a condition |
| Max | Largest of original values | With a filter, see the max of records matching a condition |
| Min | Smallest of original values | With a filter, see the min of records matching a condition |
| Count non-empty numbers | Count of non-empty numeric values | Count non-empty numeric records |
| Count non-empty values | Count of non-empty values, numeric or text | Count non-empty records |
| Count distinct | Count after deduplication | With a filter, count distinct categories matching a condition |
| Row count | Count of source records | With a filter, count records matching a condition |
| Concatenate as text | Join displayed values with comma separator | Reference a multi-select and join its options into text |
Common scenarios
Look up a product code by name
Use the inventory table as the source, reference the product code, match on product name to find the corresponding code.Calculate sales per store
Use the order detail table as the source, reference pre-sale revenue, filter by category, and sum the revenue per category.FAQ
- Q: Can a lookup field reference multiple fields? A: No — one field per lookup. Add multiple lookup fields to reference different fields across different tables.
- Q: What filter conditions are supported? A: Currently equals / not equals / contains / is empty / is not empty / earlier than / later than (date fields only).
- Q: How many filter conditions can I add? A: Up to 5.
- Q: Can I use lookup in AI Tables embedded in docs and sheets? A: Yes. You can add multiple tables in a doc or sheet and look up across them.
- Q: Which fields can be referenced or used as filters? A: Every AI Table field type can be referenced — the lookup keeps the source’s format. If the referenced field is a one-way or two-way link, linked records are deduplicated by default. Every field type can also serve as a filter.
- Q: Can I look up hidden fields? A: Yes — hidden fields appear in the dropdown of the lookup configuration; pick one to use as source or filter.
- Q: Are lookup results live? A: Yes. When the source data changes, the lookup updates automatically — no manual refresh.
- Q: Can I look up across AI Tables? A: Not yet. Lookup works within the same AI Table (or doc / sheet file).
- Q: Why can’t I edit a lookup field’s value? A: Lookup values are auto-generated from the source — edit them in the source table.