Hi
I have sheet 1 with f_name, l_name, and Date of Birth
I want to match the same 3 from sheet 2 and then bring a value gender from sheet 2 into sheet 1
I know how to reference only one column, but do not know how to match 3 columns
Thanks
Hi there,
You can use a reference column to pull data from the referenced table using dot notation. To pull data the other direction, you can use a lookup formula.
I created an example for you here: https://public.getgrist.com/ncx4AZFSepMt/Community-1380/m/fork
Dot Notation: When a table has a reference column, you can pull more data from the referenced table. In Dot_2, full_name is a reference column that pulls data from the Dot_Notation table.
The label we see is full_name but a reference column points to the entire record. To add more columns containing data from the referenced table for the record we see in the full_name column, click the green ‘+ Add Column’ text under ‘Add Referenced Columns’ in the right-hand column configuration panel. Be sure to select the reference column in the table first for these options to be available.
Any columns from the referenced table will be available to add from this dropdown.
This adds a new formula column to the table. The formula follows the format $[REF_COL].[COL_B] where [REF_COL] is the name of your reference column, in this case it’s full_name and [COL_B] is the name of the column in the referenced table that you wish to pull data from. In the screenshot below, we want to pull from the date_of_birth column in the Dot_Notation table so [COL_B] would be date_of_birth.
LookupOne / LookupRecords: Allows you to lookup data in another table based on any constraints given.
In our first example, all data was entered in a single table for each person. Then the second table referenced all this data. In this example, Table2 references Table1 data and uses dot notation to pull other data values for the referenced record, like we saw above. But now, data is entered for Gender on Table 2 (rather than Table1). We want to pull that Gender value from Table2 to Table1. This is what we call a reverse lookup. Our tables are linked via the reference column in Table2 but we want to pull data in the opposite direction of the linking.
The formula we use in the Gender column of Table1 is:
Table2.lookupOne(Full_Name=$id).Gender
This follows the format [Table_Name].lookupOne([A]=$id) where [Table_Name] is the name of the table you want to lookup data in. In this case, we want to look up data from Table2. [A] is the reference column in the table being looked up (named at the beginning of the formula). In this example, our reference column in Table2 is labeled Full Name.
Lookup formulas lookup records, similar to how a reference column references a record. We need to use dot notation to specify what column of data we want for the record. In this example, we want to find the value in the Gender column for the record in Table2 where the ID chosen in the reference column (Full Name) matches the ID of this row in Table1.
This is a bit easier to understand visually by changing the label we see on the reference column, Full Name to ‘Row ID’. The ID is what is stored in a reference column field. We choose the label under ‘Show Column’ but under that label, it’s just storing the ID.
I have added a column to Table1 to display the assigned Record ID. You can do this with the formula = $id. You’ll see that the value in the reference column, Full Name for the last record in Table2 is pointing to the record with Row ID = 2 in Table1. This is Alivia Brekke. We see that the data across all columns match correctly for this record.
I hope this helps! Please let me know if you have any other questions.
Thanks,
Natalie
1 Like
thank you so much for your wonderful explanation
1 Like