Returning specific values based on multiple criteria in a tablewith duplicate entries

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!!!!!!

Thanks a bunch! You have no idea how much you've helped me expand beyond that block I was having!

I've modified the formula you gave and have written the following in column I (referencing column H);

=IFERROR(EDATE((IF(H2="Completed",IFERROR(VLOOKUP(A2,'DoC Results'!$C$2:$AF$581,15,FALSE),""),"")),12),"")

This gives me a due date.

Problem 1 solved!

I'd like to add another condition to the vlookup. ATM, the formula looks up the Salary number and returns the first date that it finds.

I'd also like it to add another condition, so that when it finds the salary number, it checks to see IF the Course code matches (COMP001-AMLCTF, COMP005-DTRT, COMP008-TCoU, etc)

If both conditions are true, then it will return the date.

Experimenting with vlookup & IF variations now...but any help would be appreciated! I've attached a modified version of the spreadsheet...

Cheers!

Thanks a bunch! You have no idea how much you've helped me expand beyond that block I was having!

I've modified the formula you gave and have written the following in column I (referencing column H);

=IFERROR(EDATE((IF(H2="Completed",IFERROR(VLOOKUP(A2,'DoC Results'!$C$2:$AF$581,15,FALSE),""),"")),12),"")

This gives me a due date.

Problem 1 solved!

I'd like to add another condition to the vlookup. ATM, the formula looks up the Salary number and returns the first date that it finds.

I'd also like it to add another condition, so that when it finds the salary number, it checks to see IF the Course code matches (COMP001-AMLCTF, COMP005-DTRT, COMP008-TCoU, etc)

If both conditions are true, then it will return the date.

Experimenting with vlookup & IF variations now...but any help would be appreciated! I've attached a modified version of the spreadsheet...

Cheers!