How do you match multiple columns in Calc?

[lkjandersen]

Hi. I am new to these formulas, so I thought I would ask here, see if you could help.

I am trying to combine some information that is set up like this

Header 1 Header 2 Header 3

Title 1 - Number 1 - Type 1

Title 2 - Number 2 - Type 2

Title 3 - Number 3 - Type 3

And another that is set up similarly

Header 1 Header 2 Header 3

Title 1 - Number 1 - Also x

Title 2 - Number 4 - Also y

Title T - Number q - Alsoe z

Title 3 - Number 3 - Also w

Title 2 - Number 2 - Also k

I am trying to find a way to combine this information to make it say, as the endresult

Header 1 Header 2 Header 3

Title 1 - Number 1 - Type 1 - Also x

Title 2 - Number 2 - Type 2 - Also k

Title 3 - Number 3 - Type 3 - Also w

I found this formula, =IFNA(VLOOKUP($A2;$G$2:$G$6000;1;0);""), that someone graciously offered someone else on here, where the first Header 1 is A and the second is G, to be able to match each other, and show the match, but is there a way to modify it, or another formula, so that matches both headers 1 and 2 and copies the ones here marked Also under header 3 along with it?

Calc 4.3.0.4, Windows 8.1

[Lupp]

(An appeal:) Henceforth, if possible at all, please supply a functional, completely significant and relevant example as an ods. It should be functional as far as you could go (without formulae behind the expected results). Editing: Sorry, just found your karma wasn’t sufficient yet for attaching files.

(Answer:) You will need corresponding compound keys into both of your tables - either explicitly or implicitly generated and used looking for matches. The last step may be done using VLOOKUP() or a combination of INDEX() with MATCH(). The last suggested solution should be the most flexible in specific if the ranges for the match/lookup are parametrised using OFFSET().

See attached example ask38738CompoundMatchJoin001.ods