Enter name or course to get list of either

If i have a table with Peoples names down in column A and a list of courses they teach along row 1 and fill in their competences with a 1 in a matrix style. Is it possible to create a lookup where i could either enter a name and get a list of courses a person can teach or enter a course and get a list of people that can teach it. Sorry its a bit confusing, i would upload an example but the IT here is somewhat restrictive. Thanks


Moderator note: Hello trispage. Welcome to the forum. Please read and follow our forum rules. In particular Rule#1 regarding thread titles. As you are a new member I changed it for you this time as a courtesy.

You can use Power Query (Get & Transform Data) to normalise your data. Format your input data as a table, then use this query:

Please Login or Register  to view this content.

Close and Load To the Data Model.

Now you can create a couple of simple pivot tables based on the data model, to return names matching courses / courses matching names.

See attachment for a worked example, using some thoroughly random data.