Struggling with Lookup formula

Raphael Lng

Hello everyone,

I am struggling with a formula and I would really appreciate your help. Here is my idea:

Sheet 1 is a Trainings Catalog, where I have two columns: "Mandatory" and "Option". I complete these columns with the job positions concerned.

Sheet 2 is a database which contains employee information, such as the job position, and then where the trainings are assigned. Basically there will be for each employee as many rows as there are trainings.

I would like a formula which look the Job position of the employee in this sheet. If this job position is mentioned in the training catalogs for the Training A for example , I would like a formula to automatically check the box in the columns "Mandatory" and "Optional".

I know I would need to reference the sheet "Training catalog", but I have now idea then which formula could make the job !

Could someone help me ?

Thanks a lot

Raphael

Paul Newcome

You would use something along the lines of...

=IF(COUNTIFS({Reference Sheet Mandatory Column}, HAS(@cell, [Job Position]@row))> 0, 1)