LOOKUP fields are a Bitable field type based on the LOOKUP function. You can use these fields to quickly find and reference data from other grids that meets specified conditions.
Note: Currently, only data from other grids in the same Bitable can be searched for.
II. LOOKUP field vs. LOOKUP function
First, we must understand the relationship and differences between the LOOKUP function and LOOKUP fields.
LOOKUP(search value, search range | search result array, [result range])
The LOOKUP function is a common search function used in sheets. It allows you to find values in other rows/columns that match set conditions.
In the figure below, products and product types are shown on the left. On the right, we use the LOOKUP function to find product names that match the various product types associated with the list of products on the left.
- •Formula: =LOOKUP(D2,B2:B4,A2:A4)
- •Explanation: The data in cell D2 is the condition, which is used to match data in the range B2:B4 and return matching data for the corresponding data rows in the range A2:A4.
- •Result: Cell E2 shows Coffee Beans, Cell E3 shows Coffee Maker, and Cell E4 shows Napkins.
LOOKUP fields use the same logic as the LOOKUP function. However, you do not need to enter formula parameters in the LOOKUP field. You can simply click to set Search Conditions and Search Content in the LOOKUP panel and set the display format of search results.
Note: The result is demonstrated in the figure below. Read on for more information.
Create a LOOKUP field
- •Grid view: Click + in the upper-right corner of the interface, select LOOKUP as the field type, and click Confirm.
- •Kanban view & Gallery view: Click to expand a card. At the bottom of the card, click New Field, select LOOKUP as the field type, and click Confirm.
- •Gantt view: Right-click an existing field or column, insert a field/column to the left or right, select LOOKUP as the field type, and click Confirm.
Search for and reference data (key step)
In the LOOKUP panel, you must select five options.
- •Column to search - Select grid: Select the source grid that contains the data you want to search for.
- •Column to search - Select column: Select the field/column in the source grid that contains the data you want to search for.
- •Conditions for search - Select column (left): Select the field/column in the current grid to use as the search conditions.
- •Conditions for search - Select column (right): Select the field/column in the source grid in which to search for data matching the search conditions.
- •Search result display method: Select the method used to display the search results.
- ◦Original value: Displays all source data meeting the search conditions in the same way it is displayed in the source.
- •Sum: Display the sum total of matching data.
- •Count: Display the number of instances of matching data.
- •Average: Display the average of matching data.
- •Maximum: Display the maximum value from among the matching data.
- •Minimum: Display the minimum value from among the matching data.
Grid 1: Task Breakdown Table, which lists individual tasks and the people responsible for them.
Grid 2: Task Summary Table, which lists the tasks each person is responsible for.
You can perform the following operations:
- 1.Column to search - Select grid: Select the Task Breakdown table as the source grid in which you want to search for data.
- 2.Column to search - Select column: Select the Task Description column as the source column in which you want to search for data.
- 3.Conditions for search - Select column (left): Select the Implemented by column in the current table as the search conditions to search for the tasks for each person in the source grid.
- 4.Conditions for search - Select column (right): Select the column in the source grid from which to search for matches to the Implemented by conditions in the current grid. This is the Implemented by column in the task breakdown table. This way, the information in the two Implemented by columns are matched to find all tasks for each person.
- 5.Search results display method: Select Original to display the matching information just as it appears in the source.
Change the display method of LOOKUP results
Double-click a column header to select a new search results display method.
Delete a LOOKUP field
Right-click a LOOKUP field and select Delete Field/Column at the bottom of the menu.
- •Project management: Each task is associated with a team's OKRs and then a specific operator
Using an association field, you can associate each task in the Task Management Table with a team's OKRs. Then, based on this association, you can find the owner of each task and display the person's name found in the team Task Summary Table in the Task Management Table.
- •Content management: Categorize and organize multimedia files
You can use a LOOKUP field to find attachments based on the progress status of multimedia files and then display the number of attachments of each status in the appropriate cells. Create a column and apply the same search conditions. Change the result display method to Count to display the number of attachments in each status.
- •Budget management: Quickly calculate various expenses
You can use the LOOKUP field to find the product names in a procurement management table that match different procurement types.
Can I use LOOKUP fields in Bitables in Docs and Sheets?
Yes. You can add multiple girds in a Doc or Sheet and then use LOOKUP fields to connect data between the grids.
What fields can be searched for?
All fields in Bitables can be used for the lookup and display of source data, including single selection/multiple selection, attachment image, and association fields.
Can hidden fields be searched for?
Yes. Hidden fields can be selected from the drop-down list in the LOOKUP panel for use as source data or search conditions.
Are the search results updated in real time?
Yes. The search results are updated in real time. If the source data changes, the search results based on the source data will be updated accordingly.
Can I search for data across different Bitables?
This is not currently supported. At present, LOOKUP fields can only be used in a single Bitable, Doc, or Sheet.