1:many:many reverse report

[Mazerati]

Hello

I have two tables which combined have a 1:Many:Many relationship as shown in the attached example file.

I have tourists that have visited destinations, and destinations with attractions. The exam question is "Which attraction types were visited by which Tourist?"

I tried filtering (see below and in attached) however I can only get that to work for a specified attraction.

=UNIQUE(FILTER(B11:B21, ISNUMBER(XMATCH(C11:C21, FILTER(E11:E21, F11:F21=K11)))))

My actual data has many many more tourists, destinations and attractions so that is impractical.

Thank you for your help!

Kind regards,

Marek

[#2]

@Mazerati 

=DROP(REDUCE("",UNIQUE(E4:E14),LAMBDA(x,y,VSTACK(x,IFNA(HSTACK(y,SORT(UNIQUE(XLOOKUP(FILTER(D4:D14,E4:E14=y),B4:B11,A4:A11)))),y)))),1)

With Office 365 or Excel for the web you can apply this formula.

[#3]

@OliverScheurich Thank you so much, that worked perfectly. And it looks like a very smart solution, the approach is new to me. Shows you have a deep understanding of the subject. Kind regards, Marek