Struggling with Lookup formula

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
Answers
-
You would use something along the lines of...
=IF(COUNTIFS({Reference Sheet Mandatory Column}, HAS(@cell, [Job Position]@row))> 0, 1)
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66.4K Get Help
- 431 Global Discussions
- 152 Industry Talk
- 492 Announcements
- 5.2K Ideas & Feature Requests
- 85 Brandfolder
- 155 Just for fun
- 76 Community Job Board
- 504 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 307 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!