Okay here goes;
I have a table that contains training data received from the business. I need to cross reference an employee list with this table and have it return values based on multiple criteria. I've tried combining VLOOKUP and IF, I've also tried using the INDEX, MATCH functions and I've made some unholy combinations of many others.
The one that works the best seems to be a combination of IF & COUNTIFS.
=IF((COUNTIFS('DoC Results'!$C$2:$C$581,A4,'DoC Results'!$U$2:$U$581,"Completed"))>0,"Completed","Incomplete")
True values are returned in all my tests.
First problem;
In the next column (beside the one in my forumla), I'd like to display a specific column value (it's a date) from any records that meet the criteria i.e =1
Second Problem;
Some employees have done the same training twice i.e. >1. I'd like the above formula to return the most recent date of all the records that are counted. I've read about the MAX function but can't use it until I have a formula that returns a value (Problem 1)
I'd love to post a sample of the workbook, but it'll take more time than I'm willing to give to mock-up values. Let me know if you want me to post the actual spreadsheet.
Please help!!!!!!
Bookmarks