Pull a column of data from one sheet to another based on a matching criteria

Question

I am new to Google sheet formula and need some help from the community.

I have two tabs from a spreadsheet, A and B:

• Both tabs basically have the same number of columns (col1, col2 and col3) except that col3 of B is all empty
• In terms of records, A is a subset of B. All the records in A should have matching records in B using col1 and col2.
• The orders of records in A and B are different which makes the matching more complicated

What I wanted to do is to pull col3 from A and to copy its values to corresponding records in B.

Here are visual examples:

         A
+------+------+------+
| col1 | col2 | col3 |
+------+------+------+
|  10  |  12  |  11  |
+------+------+------+
|  9   |  8   |  10  |
+------+------+------+

         B
+------+------+------+
| col1 | col2 | col3 |
+------+------+------+
|  10  |  12  |      | <-- this should have 11 from A
+------+------+------+
|  9   |  8   |      | <-- this should have 10 from A
+------+------+------+
|  9   |  7   |      | <-- this should be still empty since there is no matching record.
+------+------+------+

How can I do this by using google sheet formula?

I tried FILTER function and but I wasn't sure how to specify the matching criteria.

Answer 1

One way of doing what you want is to create a new column on your spreadsheet A, that would contain the combined columns A & B. You would move your original spreadsheets to column B-D and column A1 would be = A1&B1.

Then you can use a simple lookup on your spreadsheet B, that would look like:

=VLOOKUP(A1&B1,A!A1:D10,4,false)

Good luck!

Answer 2

Isn't it a simple task for:

=index(A!A1:A)

or did I not understand something?

EDIT

Following your comment, you can insert this formula in B!C1, which does not require the insertion of other columns in A! (nor in B!) and that will automatically fill in ALL cells of column C of B!:

=ifna(index(vlookup(B!A1:A&B!B1:B,{A!A1:A&A!B1:B,A!C1:C},2,false)))

link to test file