1

I'd like to fully join 2 tables on 3 key values, there doesn't need to be 0 value on blank cells if it makes it easier to understand (Some Array Formulas can be really confusing)

I have income:

enter image description here

I have expense:

enter image description here

I'd like to fully join the 2 tables so it results like this:

enter image description here

How can I achieve this in Google Sheets?

Here's the example sheet: https://docs.google.com/spreadsheets/d/1O1rZUstDNSXPdUVXvaDfPO4rAQs2cJWHimfGxbddtNU/edit#gid=490516150

1

=ARRAYFORMULA({QUERY(SORT(UNIQUE({Income!A2:C; Expense!A2:C})), "where Col1 is not null"),
 {IFERROR(VLOOKUP(TRANSPOSE(QUERY(TRANSPOSE(QUERY(SORT(UNIQUE({Income!A2:C; Expense!A2:C})),
 "where Col1 is not null")),,999^99)), 
 {TRANSPOSE(QUERY(TRANSPOSE(Income!A2:C),,999^99)), Income!D2:D}, 2, 0), 0),
  IFERROR(VLOOKUP(TRANSPOSE(QUERY(TRANSPOSE(QUERY(SORT(UNIQUE({Income!A2:C; Expense!A2:C})),
 "where Col1 is not null")),,999^99)), 
 {TRANSPOSE(QUERY(TRANSPOSE(Expense!A2:C),,999^99)), Expense!D2:D}, 2, 0), 0)}})

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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