How to write excel formula to return column header name-CodePudding

I am looking to allow users to select the modules. Diving deeper, the users can select base on syllabus. Thereafter, it will tell them which courses (aka the column header), which matches the condition base on the rows with "Y".

Example  If the following is selected:
- Data Governance (Policy and Process)
- Data Analytics Project (Problem Statement Formulation)
- Data Exploration (Exploratory Analysis)
- Data Exploration (Machine Learning) 
 It will all return both CourseID1 & CourseID2,  If Data Analytics Project (Data Handling & Ethics) selected it will not return CourseID1. Only CourseID2 will be returned

I have attached a screen shot of the Excel Table. The flow is as follows: step 1: user selects module step 2: user select the syllabus that's tied to the module (a dependent selection from step 1) step3: Output for the suitable courses is shown!

How to write such formula?

The formula I used was wrong

=INDEX($A$1:$E$1,SUMPRODUCT(MAX(($A$2:$E$72="Y")*($B$2:$B$72=B2)*(COLUMN($A$2:$E$72))))-COLUMN($A$1) 1)

It does not return both CourseID1 and CourseID2 when the right criteria is being selected.

CodePudding user response：

So, this is what I have tried, assuming you are using O365

• Formula used in cell A17 using FILTERXML(), XLOOKUP() , TEXTJOIN() , INDEX() , SUBSTITUTE() & COLUMN() functions.

=TEXTJOIN(", ",,INDEX($A$1:$E$1,,INDEX(--(XLOOKUP(
FILTERXML("<p><w>"&SUBSTITUTE(SUBSTITUTE($A12,"&",""),", ","</w><w>")&"</w></p>","//w")&
FILTERXML("<p><w>"&SUBSTITUTE(SUBSTITUTE($A15,"&",""),", ","</w><w>")&"</w></p>","//w"),
SUBSTITUTE($B$2:$B$9,"&","")&SUBSTITUTE($C$2:$C$9,"&",""),$D$2:$E$9)="Y")*(COLUMN($D$1:$E$1)),1)))

Or, If you are using O365 and presently in Office Insiders Beta Channel Version then using TEXTSPLIT() instead of FILTERXML()

• Formula used in cell A18

=TEXTJOIN(", ",,INDEX($A$1:$E$1,,(TRANSPOSE(XLOOKUP(
TEXTSPLIT(A12,,", ")&TEXTSPLIT(A15,,", "),$B$2:$B$9&$C$2:$C$9,
$D$2:$E$9))="Y")*COLUMN($D$1:$E$1)))