LOOKUP field is a Bitable field type developed based on the LOOKUP function. You can use LOOKUP field to quickly find and reference data from the same or other tables that meet specified conditions.
Note: Currently, only data within the same Bitable can be referenced.
Function: LOOKUP(search key, search range | search result array, [result range])
The LOOKUP function is a common search function used in spreadsheets. It allows you to find values in other rows and 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 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 Machine, and Cell E4 shows Napkins.
LOOKUP fields use the same logic as the LOOKUP function. However, you don't 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.
Step 1: Create a LOOKUP field
- ◦Click + in the upper-right corner of the grid.
- •Select LOOKUP as the field type.
- •Kanban and Gallery view:
- ◦Click to expand a card. At the bottom of the card, click + New Field.
- •Select LOOKUP as the field type.
- ◦Click the Customize field button on the toolbar.
- •Click + New field, and select LOOKUP as the field type.
Step 2: Set up LOOKUP conditions
To set up LOOKUP conditions, you must select five options:
- ◦Select table: Select the source table that contains the data you want to refer to.
- •Select field: Select the field in the source table that contains the data you want to refer to.
- •Search condition: Bitable will match this field with the Selected field in source table.
- ◦Select field (left): Select the field in the current grid to use as the search conditions.
- •Select field (right): Select the field in the source grid in which to search for data matching the search conditions.
- •Rresult array: Select the method used to display the search results.
- ◦Value: Displays all source data meeting the search conditions in the same way it's original displayed in the source.
- •Sum: Display the sum of all matching data.
- •Count: Display the number of instances of matching data.
- •Average: Display the average value of all matching data.
- •Maximum: Display the maximum value from among the matching data.
- •Minimum: Display the minimum value from among the matching data.
Table 1: Task Management table, which lists individual tasks and who's in charge.
Table 2: Team OKR task, which lists the tasks each person is responsible for.
You can perform the following operations:
- 1.Search range - Select table: Select the Team OKR Tasks table as the source grid in which you want to search for data.
- 2.Search range - Select field: Select the OKR column as the source column in which you want to search for data.
- 3.Search condition - Select field (left): Select the Owner field in the current table as the search conditions to search for the tasks for each person in the source grid.
- 4.Search condition - Select field (right): Select the Task leader column in the source grid from which to search for matches to the Owner field in the current grid.
- 5.Result array: Select Value to display the matching information just as it appears in the source.
Step 3: 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 at the bottom of the menu.
Project management and OKRs
Using a Relation field, you can associate each task in the Task breakdown table with a team OKR. Then, based on related record, you can LOOKUP the owner of each OKR and display the person's name found in the Team OKR Tasks table.
Content management: Categorize and organize multimedia files
You can use a LOOKUP field to find attachments and count multimedia files. Create a LOOKUP field and apply LOOKUP 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 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 tables in a document or spreadsheet and then use LOOKUP fields to connect data between the tables.
What fields can be looked up?
All fields in Bitables can be used for the LOOKUP and display of source data, including single or multiple selections, attachment images, and Relation fields.
Can hidden fields be looked up?
Yes. Hidden fields can be selected from the drop-down list in the LOOKUP panel for use as source data or search conditions.
Are search results updated in real-time?
Yes. Search results are updated in real-time. If the source data changes, search results based on the source data will be updated accordingly.
Can I search for data across different Bitables?
No. This isn't currently supported. At present, LOOKUP fields can only be used in a single Bitable, document, or spreadsheet.