Lookup field overview

A lookup field allows you to pull record contents from one linked record into another linked record. As a reminder, linked relationships can be either within the same table or across two separate tables.

You could design a self-linking linked record field in an "Employees" table that could specifically look up an employee's immediate supervisor based upon the dependencies set in the linked record field. Alternatively, if you have an "Orders" table with a field linking to "Clients," you could create a lookup field that displays the linked client's address. In other words, the address from the "Clients" table is brought into the "Orders" table.

Introduction

When to use a lookup field

A lookup field is most useful when you want to cross-reference information that's stored in separate tables. As an example, let's say you're using Airtable for project management, with projects stored in one table and tasks related to that project stored in another. Each project is linked to a set of tasks.

As a project manager, you might want to view information about the tasks associated with each project. A lookup field can be used to select a field in the linked table and display its values. In the example below, the assignees for all tasks in a single project are listed in a lookup field.

This can be helpful for easily accessing information across tables, setting up automations, formulas, and more. Follow along below for more details on how to use the lookup field.

How to use the lookup field

Add a linked record field

The lookup field requires the use of a linked record to create a link between two tables. In order to use a lookup field, start by configuring a linked record field.

Add a lookup field

Then, add a new field and select the lookup field type.

Configure the lookup field

If you have multiple linked record fields, you'll have the option to select which of those linked record fields to use for the lookup. Once you've made that selection, pick the cells from those linked records that you want to display.

Add lookup conditions

As an optional step, you can select the option to only include linked records from your linked table that meet certain conditions. Using the example above, you may only want to display campaign leads for campaigns that are in a "Planning" or "Live" stage. You can use lookup conditions to specify that criteria, and the field values will only display for records that match the criteria.

Adding multiple lookup fields in bulk

You can easily add multiple lookup fields by right clicking on the name of your linked record field. Then, click the menu option to Add lookup fields.

This will bring up another menu where you can select specific fields from the linked table to add as lookup fields.

Lookup fields and formula fields

There are certain situations where you'll want to modify the way information returned from a lookup field appears. A great example of this is a linked record field with multiple records.

If there are multiple linked records, the lookup will concatenate the cell values and separate them with a comma.

While it isn't possible to change the comma separator to another symbol, you can use a rollup field instead of a lookup to do so.
For this approach, add and configure a rollup field similar to the lookup field instructions above. Then, in the rollup aggregation formula, paste the following: SUBSTITUTE(ARRAYJOIN(values), ",", " + "). This instructs the rollup field to substitute the comma separator with something like the + sign instead. You can switch out the + sign in this formula with any symbol you'd like to use as a separator.

So, if you find yourself wanting to change the information returned from a lookup field(like you'd do with a formula), we recommend looking into the Rollup field to accomplish your goal.

FAQs