multiple criteria nested lookup formula - only the strong need apply

Hey guys, First of I would like to says thanks in advance to anyone who can help with this. I am creating a training matrix for my shop and I want to make it as automated as possible, using a complicated formula that I am struggling to get down. I thought I would throw this out there and see if anyone wants to take a shot at it. I drew out a flow chart of what the formula needs to check for, and what it should return based on what it finds. I've attached this flow chart to this post along with the spreadsheet. Basically on the page "Job Requirements (Static)" I have a list of positions within the company, with a list of job duties at the top. For every position that requires training on a specific job duty it will be marked "required". This is a static page that doesn't change. A second page ("All Training Completed") is just a list of all training received by all employees with the date completed, whether it expires or not, the expiration date if it does, and whether they are a trainer or not. The only thine that determines if they are a trainer is that if three months have passed since they were trained and a few duties do not allow trainers. On another page ("Matrix") I have the same setup as the static job requirements page, but this time with individual employee names, and their position in the company next to it. Here is where the formula will be entered. After creating the flow chart I realize some things may be out of order. I have it searching for expiration first, and it may need to search for "are they trained" first. But anyway, It needs to search the employee name and each job duty and tell me if they are trained, was that training required, does it expire, are they a trainer, or do they need training. It's a little difficult to type out, the flow chart definitely helps explain it. Basically I just want to be able to put training given into the "all training completed" list and not have to go back and manually find out whether they need to be re trained or manually change them to trainers from competent.

Please if there are any questions to this let me know and once again, I really appreciate any help given on this.


training formula flow chart.pdfonline posted training matrix.xlsx

Unfortunately your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution.

Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.

To change a Title go to your first post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)

Hi,

I for one find it a little difficult to marry your text description with the workbook and understand the various relationships. It would probably help considerably if you could manually add some typical results using different permuatations/sets of data and explain in notes how you have arrived at your results by referencing the cells/ranges that provide them.