We will be migrating from Ask to Discourse on the first week of August, read the details here

Ask Your Question

How do you match multiple columns in Calc? [closed]

asked 2014-08-23 14:24:52 +0200

lkjandersen gravatar image

updated 2020-07-22 08:29:03 +0200

Alex Kemp gravatar image

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, Windows 8.1

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2016-03-02 22:06:29.463069

1 Answer

Sort by » oldest newest most voted

answered 2014-08-23 22:53:58 +0200

Lupp gravatar image

updated 2014-08-23 22:57:19 +0200

(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

edit flag offensive delete link more

Question Tools

1 follower


Asked: 2014-08-23 14:24:52 +0200

Seen: 4,143 times

Last updated: Aug 23 '14