I concur with Mark - this is too complicated. Your most recent description now makes reference to tables for (1) Audits, (2) Requirements, (3) Questions and (4) Checklists and you are contemplating adding yet another table for (5) Procedures and more many to many relationships. I think you would be best served by going offline for a time with or without Mark's help and documenting your tables, relationships and critical fields along with your desired workflow and reporting requirements. Again I would present this schema information without regard to how you would eventually solve the problem using native features.
With regard to what I think is the nucleus of your inquiry ("I need to determine overlap between two multi select text fields") I doubt you are going to find a native solution without a large number of artificial relationships that shuffle information between tables by "looking up" and "look downing" along the table to table relationship. The overlap that you are seeking to identify and report on between two multi select text fields is not something that can easily be done with native queries and formulas but is almost trivial to do using script. In fact using script determining the overlap (ie intersection) is a one line of code once the relevant data is accessed through the API.
The bottom line is that you need to solidify your tables, relationships and critical fields and then use script to calculate the overlap between the multi-select fields (or between the same information modeled as child records).
