Google Sheets linking two sheets using a primary key

Question

I have two sheets that I want to link using a "primary key". At the moment, I have imported from sheet1 into sheet2 some columns using the function =Sheet1!A1 (for the first cell for instance). My purpose is to complete the data related to each imported line in sheet2. However, sheet1 is shared with other people, thus they can modify the content of a line without deleting or modifying the data I have added in sheet2 (and that doesn't exist in sheet1).

Given the fact that my table has a column 'id' that can be considered as a primary key, how can I add new data in sheet2 in so far as it will be related to the 'id' rather than the position of the line (so if I ever change an id in sheet1 the data I added will be deleted or if I move an id to another line, all the data will be moved too)?

player0 · Accepted Answer · 2019-02-27T12:17:11.217

5

you can use VLOOKUP and bring over data based on ID, like:

=ARRAYFORMULA(IFERROR(VLOOKUP(D:D, Sheet1!A1:B, 2, 0),))

for more columns add them in an array:

=ARRAYFORMULA(IFERROR(VLOOKUP(D:D, Sheet1!A1:B, {2,3,4,5}, 0),))

demo spreadsheet

edited Feb 27 '19 at 12:17

answered Feb 25 '19 at 14:12

player0

124,011
12
67
124

Thanks for your answer, but it doesn't help me creating the link between the 'id' column and the others. Let me clarify my purpose: actually, I want to modify a cell by taking into account the value of the 'id' in the same row. Wich means I won't be modifying the cell according to its position but rather according to the value of another cell in the same row and a different column. I don't know if I'm clear enough. Any idea please? – IMC77 Feb 27 '19 at 10:57
2

well, yes, its clear and thats what the answer suggests. here I made you an demo spreadsheet so you can test it: https://docs.google.com/spreadsheets/d/1wJHdJAgSZKwqQW1WfQ983iE0WO7ojwiXJq7CTGpC8Mc/edit#gid=694926097 – player0 Feb 27 '19 at 12:16

score 0 · Answer 2 · answered Feb 17 '23 at 16:46

I like player0's answer, but I found this easier to understand:

=QUERY(data_to_search,"select column_you_want_to_show where other_sheets_primary_key_column='"&this_sheets_primary_key_column&"'")

e.g.

=QUERY(Sheet4!$A$2:$F$10,"select F where A='"&$A2&"'")

Google Sheets linking two sheets using a primary key

2 Answers2