Excel: Generate lists from matrix

Question

At my place of work we use a matrix in Excel to keep track of which ROLES should have which RIGHTS. The ROLES are written on the column headers and the RIGHTS are written on the row headers. If one of the ROLES has any text (usually an "x") next one of the RIGHTS (at the intersecting cell) it means that this role can have this right.

I want to be able to select one of the ROLES from a dropdown menu on another sheet in the same workbook and have Excel generate a list of which RIGHTS this role should have. It would also be very convenient if we can do this the other way around. (Meaning: Selecting one of the RIGHTS to get a list of the ROLES that have this right.)

I was able to create dropdowns with conditional formatting but I can't seem to get the rest of the logic to work. I have tried writing functions and I have been messing around in VBA, but to no avail. I've tried a couple of prompts on ChatGPT, but all I get is useless nonsense.

Any insight in how to achieve this would be greatly appreciated!

Answer 1

Like this, for example.

The formula in B14 is

=FILTER(B3:B9, INDEX(C3:H9, 0, XMATCH(B13, C2:H2))<>"", "")

And the formula in E14 is

=TRANSPOSE(FILTER(C2:H2, INDEX(C3:H9, XMATCH(E13, B3:B9), 0)<>"", ""))

Answer 2

Yes! This was exactly what I needed. After some modifying I got it to work with my ranges on another sheet. I couldn't find how to do this by searching the web, so hopefully your reply will also help many other with this same problem. Thanks a lot!

Answer 3

Hi,

In cell B14, enter this formula

=FILTER(B3:B9,XLOOKUP(B13,C2:H2,C3:H9)<>"")

In cell D14, enter this formula

=TOCOL(FILTER(C2:H2,XLOOKUP(D13,B3:B9,C3:H9)<>""))

Hope this helps.