Display table of data based on lookups

Below is the screenshot of the page I am trying to automate:

Jobs & Skills.jpg

In B3 of "Query"


=IFERROR(INDEX(Employees!$B$1:$O$1,,SMALL(IF(INDEX(Employees!$B$2:$O$5,MATCH($B$1,Employees!$A$2:$A$6,0),0)="x",COLUMN($B$1:$O$1)-COLUMN($B$1)+1,""),ROWS($A$2:A2))),"")


...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

What are the criteria for selecting the course?

COSHH for example applies to all the selected roles for "Alice" as does "Site Safety"

Is it any course which covers ALL roles selected?

... and update your profile about Excel version. Now it is not compatibile because Ex2003 doesn't support xlsx extension.

As any course can cover multiple roles e,g COSHH it is notclear to me how the courses in you sample have been selected:

why was "Stacking of equipment" selected but not (for example) "Movement of Vehicles in Yard" ?

Hi John,

Good spot, the courses vary by Type - so some courses are talks given by line managers and others are simply handouts to read / forms etc... I could probably remove the safe systems of work type - does this help?

Kind regards,
Paul

I found 15 courses which cover (applied to) each of the roles in your example ... : these appear to be "generic" i,e Apply to all roles,

COSHH
Site Safety Induction
Manual Handling
Noise
H&S Law - What you need to know & do
Depot Housekeeping
PPE
Eye Protection
Noise & Hearing Protection
Use of Ladders & Step Ladders
Manual Handling
Site Welfare
Dust and Fumes
Accident Prevention
Hand Protection

I assume the above can be filtered by removal of "safe systems of work type" ?

By contrast, "Stacking of equipment" applied only to 4 (of the 8 roles in the sample file).

Sorry, but the selection criteria is not obvious to me!

Hi Sandy, thanks but I can't seem to get it working here

I was thinking perhaps it might be easier to hide the rows that don't apply ?

I understand you cannot install PowerQuery add-in.
No prob.
So wait for John's solution or any other person.

Dear All - I've also uploaded a second file (v2) - trying to get at a solution

Does anyone know what formula will return a Yes / No in the yellow column ?

Many thanks - this problem is troublesome for me.

Paul

In D13 of "Query"

=IF(SUMPRODUCT((Courses!$D$1:$Q$1=$B$3:$B$10)*(Courses!D2:Q2="x")),"Yes","No")

Copy down

Many thanks John - that's perfect !