The Related row lookup looks for and returns the value of a cell in an existing Related row.
The Related row lookup data type does not relate two worksheets together but allows you to look up individual values in an already linked worksheet. It looks for the value of a cell in an existing Related row, similar to a lookup function like VLOOKUP. Related row lookups bring data from source worksheets into target worksheets and help ensure that you are not duplicating data in multiple places. Related rows and Related row lookups allow you to maintain a single source of truth for each record and automatically bring that data into linked worksheets.
To enable Related row lookups, both the Related row and Related row lookup data types must be applied at the column level.
Understanding Related Row Lookups
Let’s say we have two worksheets – a Student Roster and a Gradebook – that show different information, but are related via their common “Name” records.
With Related rows, these worksheets are linked and provide easy access to each other’s data, but the data contained in each worksheet remains the same. With a Related row lookup, we can bring data from one worksheet directly into the other.
The Related row lookup in our Student Roster takes the related “Name” record, looks for that row in the linked Gradebook, and returns the “Overall Grade” value from the linked row.
The data that’s brought into our Student Roster worksheet isn’t a new record, but a related record. If we make changes to the related data in our Gradebook worksheet, the corresponding record in our Student Roster will automatically update to reflect the changes.
How to Create and Configure a Related Row Lookup
Related row lookups are created via the Related row lookup data type and can be added to your worksheet like all other data types.
Before creating a Related row lookup, your worksheet must contain a Related row column.
In the example below, we have two worksheets – Employees, our target worksheet, and Regions, our source worksheet – that have already been related via the “Region” column (Column B) in the target worksheet. The Related row here is a 2-way link.
Right now, the “Regional Manager” information is only housed in the source worksheet, but we want this to also appear in our target worksheet so that each employee record displays the regional manager under which they work. We’ll do this with a Related row lookup.
First, select the column that you want to contain the lookup values. Right-click on your selection and select “Edit data type…” from the dropdown to open the Update column dialog and select “Related row lookup” from the Type dropdown.
You’ll first be prompted to select the column in the related worksheet from where you want to lookup values. Because we’re looking up the regional manager data, we’ll select “B – Regional Manager”.
Note that Strict is enabled by default and cannot be turned off. Once you’ve selected the lookup column, click the blue Update button in the bottom right corner to save your options and convert your column to a Related row lookup.
Now, Column C of our target worksheet has been auto-populated with the corresponding values of each Related row’s “Regional Manager” column in our source worksheet.
The values in the lookup column are non-editable. Changes to the corresponding data in your source worksheet will be reflected in the target worksheet’s lookup column, but the corresponding data in your source worksheet cannot be edited from your target worksheet’s lookup column.
Related Row Lookups with 2-way Links and Multiple-Row Linking
When a Related row has been established with a 2-way link, Related row lookups can be created on both sides of the link in the target and source worksheets. Related row lookups can also work in conjunction with Related row values that are linked to multiple rows, and will return separate lookup values for each of the rows to which they are related.
Let’s take a look at our example again. This time, we’ve added a new column – Department – to our target worksheet and want to add a Related row lookup to our source worksheet that returns the information from this column.
Our new lookup column in Column D of our source worksheet is returning the lookup values for both of the related values in the adjacent Related row column, Column C. When multiple lookup values are returned, they appear as a comma-separated list.
Using Related row lookups with multiple-row linking can be especially useful when your Related rows reflect one-to-many or many-to-many relationships, but may require the use of junction tables to understand the underlying relationships behind their values.
Learn more about working with Related rows in our series of articles on relationships between worksheets. Or, read on to learn more about the rest of Spreadsheet.com's Data Types including Related row rollups.