How to use Lookup columns

For more explanations of what table relationships are, see this help center article.

Contents

• When to use Lookup columns
• How a Lookup column works
• Creating a Lookup column
• Converting a column to Lookup (auto-linking)
• Restricting selections in a Lookup column

When to use Lookup columns

Use Lookup columns to allow you to connect and show information from linked rows in another table. This is similar in concept to the VLOOKUP() formula in Google Sheets, where you can "look up" data from another row in a table (even within the same table).

Lookup columns are great for use cases where you have to link related data together and show it in a table, for example:

• If you have a table of Projects and a table of Teams, you can create a Lookup column in the Projects table to allow you to link a team from the Teams table that owns the project.
• You can also add more Lookup columns to show more information about the team that is linked to a project.

How a Lookup column works

• When creating a Lookup column, you will need to either select an existing table relationship, or create a new table relationship that links to the table you want to look up data from. The column header will show an arrow you can hover over to see the linked table and relationship name.
   
• If you edit a cell in a Lookup column, you will only be able to select records from the linked table. This also allows you to show questions in Tables forms that offer a dynamic list of options to select.
• Double click or open the right-click menu on the column header to edit and rename the column, or click on "Open in Relationships dialog" to rename the relationship, manage lookup columns, or add a relationship filter to restrict what records are shown (see further below).
• Multiple lookup columns can use the same relationship to pull more information from the other table, and you can create relationships that link to records within the same table, for representing task dependencies.

Creating a Lookup column

To create a Lookup column, you can do this with multiple approaches:

1. Through the Relationships dialog, which you open from the "Relationships" toolbar button.
2. Through the Column settings dialog, which you open when creating a regular table column.
   1. Click the "+ Add column" button (or convert an existing Text column)
   2. Set the custom column name
   3. Click to open the column type selector, and click on the "Link to table" > "Lookup" column type.
   4. Click the relationship selector, where you can:
      • Select an existing table relationship, or
      • Create a new table relationship (will need to select which table to link to)
   5. (optional) Check the checkbox to allow linking to more than one row.
   6. Select the source column you want to look up data for.
   7. (if you allow linking to multiple rows) Select what type of "summary" should show when there are multiple linked rows. Most common is to List the values, or "Count by value".
   8. Save the column configuration.
      
      

 See the first half of the following YouTube video for a guided walkthrough on how to create Lookup columns:

Note: the video was made with an older version of the Tables interface, but the general flow is the same.

Link tables and lookups with Tables

Converting a column to Lookup (auto-linking)

Tables operates on having structured column data types, which is similar to a real database. Google Sheets gives you the flexibility to look up data from any range within the sheet, and allows you to easily look up data based on an existing value in a column.

The way to achieve the same effect in Tables is to take an existing Text column and convert it into a Lookup column, selecting a source column to match the existing text values on.

See this YouTube video walkthrough for auto-linking:

Convert column to lookups

Restricting selections in a Lookup column

You can restrict what rows can be selected when editing a cell in a Lookup column, by applying a filter to the relationship that the Lookup column is configured on.

To add a relationship filter:

1. Open the relationship in the Relationships dialog, via:
   1. Clicking on the "Relationships" toolbar button and clicking on the specific relationship
   2. Editing the Lookup column and clicking on the "Open in Relationships dialog" button
2. When viewing the relationship, at the bottom of the dialog is a section where you can add filtering criteria, based on values in the linked table columns.

See this YouTube video walkthrough for using relationship filters:

With relationship filters, now you have more control when linking data between tables