1

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.

1
  • Why FILTER()? Why not SUMIFS()? Like as =SUMIFS(<$source col3>,<$source col1>,<cell in B col1>,<$source col2>,<cell in B col2>)
    – JohnSUN
    Sep 9, 2022 at 7:13

2 Answers 2

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!

1

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

lookup

2
  • Sorry. I forgot to mention one more import thing. The orders of records in A and B are different :(
    – kee
    Sep 9, 2022 at 17:18
  • I have updated my answer
    – Daniele
    Sep 10, 2022 at 8:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.