Multiple Lookup between headers/rows help

[Dani_LobP]

Hello,

I am trying some ambitious formula and can't seem to even manage Copilot to assist me to figure out a simple way. hopefuly here I will find a solution.

The context:

I have 2 tables. One with some Contacts, that have some headers, and each header represent a group of items.
Then i have another table, where i have those headers, as rows, and each one representing another group of items.
Something like the below:

Table Contacts

Contacts Names	Group1	Group2	Group3	Group4
Contact1
Contact2
Contact3

Table Items

Group	Item
Group1	Item1
Group1	Item2
Group1	Item3
Group2	Item4
Group3	Item5
Group3	Item6
Group4	Item7

In the Table Contacts, each contact will have an X on each column when they fit a group. If they have an X in group 1, it would mean that they would have items 1 to 3 assigned.

I have a 3rd table, where i have basically a copy of Table Items, but where i added all contacts as headers. To merge both tables.

And what i want, is a formula or a way, to automate the population of the data, so whenever a Contact has an X on a group in the Table Contacts, it will populate the 3rd table, for each row where the group matches.

Something as per below:

Table Contacts

Contacts Names	Group1	Group2	Group3
Contact1	X		X
Contact2		X
Contact3	X

Final Table

Group	Item	Contact1	Contact2	Contact3
Group1	Item1	X		X
Group1	Item2	X		X
Group1	Item3	X		X
Group2	Item4		X
Group3	Item5	X
Group3	Item6	X

The Final Table, is not really a table, because i would like it to have dynamic rows/headers, so whenevr a contact is added, a new header will appear, and whenever a new group/item is added, a new row would be added.
I did this part with Unique, for rows, and unique transpose for headers, but also am welcome for better options.

Hope it makes sense and look forward for any suggestion and help from you all!

thanks in advance!

 

[Eric W]

Maybe:

Book3
	A	B	C	D	E	F	G	H	I	J
1	Contact Names	Group1	Group2	Group3
2	Contact1	X		X
3	Contact2		X
4	Contact3	X
5
6
7
8
9
10					Final Table
11					Group	Item	Contact1	Contact2	Contact3
12	Group	Item			Group1	Item1	X		X
13	Group1	Item1			Group1	Item2	X		X
14	Group1	Item2			Group1	Item3	X		X
15	Group1	Item3			Group2	Item4		X
16	Group2	Item4			Group3	Item5	X
17	Group3	Item5			Group3	Item6	X
18	Group3	Item6			Group4	Item7
19	Group4	Item7
20
Sheet2

Cell Formulas
Range	Formula
E11:I18	E11	=LET(t,FILTER(A13:B25,A13:A25<>""),h,TRANSPOSE(FILTER(A2:A9,A2:A9<>"")),d,INDEX(B2:G9,MATCH(h,A2:A9,0),MATCH(INDEX(t,0,1),B1:G1,0))&"",IFERROR(HSTACK(VSTACK(A12:B12,t),VSTACK(h,d)),""))
Dynamic array formulas.

 

[Peter_SSs]

I did this part with Unique, for rows, and unique transpose for headers,

This might need further explanation &/or samples as I am not seeing anything in your sample data that would require "unique".

I am also wondering if breaking the task up a bit, making the formulas shorter, might worth it. IF you have the Trim Refs notation available in your 365 version then perhaps something like this.

25 04 12.xlsm
	A	B	C	D	E	F	G	H	I	J
1	Contacts Names	Group1	Group2	Group3	Group4
2	Contact1	X		X
3	Contact2		X
4	Contact3	X
5
6
7
8
9
10
11					Group	Item	Contact1	Contact2	Contact3
12	Group	Item			Group1	Item1	X		X
13	Group1	Item1			Group1	Item2	X		X
14	Group1	Item2			Group1	Item3	X		X
15	Group1	Item3			Group2	Item4		X
16	Group2	Item4			Group3	Item5	X
17	Group3	Item5			Group3	Item6	X
18	Group3	Item6			Group4	Item7
19	Group4	Item7
20
21
Tables

Cell Formulas
Range	Formula
E11:F18	E11	=A12:.B30
G11:I11	G11	=TRANSPOSE(A2:.A10)
G12:I18	G12	=INDEX(B2:J10,XMATCH(G11#,A2:A10),XMATCH(E12:.E30,B1:J1))&""
Dynamic array formulas.

 

[Dani_LobP]

Maybe:

Thanks a lot for your help! this worked, changed couple things to adapt it to my real table, but works as i imagined.

 

[Dani_LobP]

This might need further explanation &/or samples as I am not seeing anything in your sample data that would require "unique".

I am also wondering if breaking the task up a bit, making the formulas shorter, might worth it. IF you have the Trim Refs notation available in your 365 version then perhaps something like this.

Thanks! adapted it a bit together with Erik response.

 

[Eric W]

Glad we could help. Thanks for the feedback.