Updating spreadsheet after altering the original one

Question

I have the following data in spreadsheet A.

    name  trait1 trait2 nice
0   Adam  29     81     0
1   Barry 17     75     1
2   Chris 62     0      1

I wish to create a spreadsheet B that will be a filtered copy of this data. Namely, let's assume for a moment that I want to filter nice = 1 and am interested only in column name. The copy in spreadsheet B would be as shown below. In spreadsheet B I wish to be adding some extra columns, e.g. education.

    name  nice education
1   Barry 1    primary
2   Chris 1    university

What I want to achieve is a spreadsheet B that will get updated if anything changes in spreadsheet A. So for example, if I were to change the name Barry to Ben. The spreadsheet B would become the following.

    name  nice education
1   Ben   1    primary
2   Chris 1    university

Similarly (and what I find to be the hardest), if a row is added in spreadsheet A, e.g.

    name  trait1 trait2 nice
0   Adam  29     81     0
1   Barry 17     75     1
2   Matt  69     11     1
3   Chris 62     0      1

The updated spreadsheet B would be as follows:

    name  nice education
1   Barry 1    primary
2   Matt  1    
3   Chris 1    university

So I want the education column to remain the same.

My approach of using a combination of =IF() and =VLOOKUP() functions ultimately did not work. Guess I am really curious about how to connect rows of education to names. So that when a row is added in spreadsheet A, then spreadsheet B gets updated but the education field connected to the new row is empty and will be filled by hand later on.

Answer 1

Since you are looking for a finished product to be in Google Sheets, I'd advise to use QUERY():

Formula in I1:

=QUERY(INDEX({A:D,VLOOKUP(A:A,F:G,2,0)}),"Select Col1,Col4,Col5 where Col4=1")

---

Note: I made the assumption you pull the education in through a VLOOKUP() (since you mentioned that in the body of the question).

---