excel

Return all column headers when there are multiple yes entries from a vlookup

I'm working on a sheet that allows me to fill in a data capture sheet front end, selecting team, job role etc and then from that info generate a list of training courses they need to be booked onto.

I have a course vs role sheet with column a being job title, then the remaining column headers are course names. If the role needs to attend the course, there is a Y in the cell.

What I want to do is to be able to complete the front end form, press a button and have the joiners details pulled into a sheet that then adds the courses I need to book them onto.

I can handle the transfer of data from front end to the other sheet, but not sure about how to make it pull the course names across.

Solution

  • I am trying to answer your question based on the following data - and provide a basic formula solution

    x

    Formula in F3:

    =LET(JobTitle,F2,
data,$A$1:$D$4,
JobTitleCourses,INDEX(data,MATCH(JobTitle,CHOOSECOLS(data,1),0),),
TOCOL(FILTER(CHOOSEROWS(data,1),JobTitleCourses="Y")))

    JobTitleCourses returns the row of the according job title.

    It then is used to filter the header row

    By using TOCOL the according courses are listed below the job title

    Without TOCOL

    =LET(JobTitle,F2,
data,$A$1:$D$4,
JobTitleCourses,INDEX(data,MATCH(JobTitle,CHOOSECOLS(data,1),0),),
FILTER(CHOOSEROWS(data,1),JobTitleCourses="Y"))

    x

    But this will not work in a table!!!