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!!!!!!
Have a look at this thread and see if it helps:
http://www.excelforum.com/excel-prog...sfer-data.html
It uses the formula:
=IF($D2="Yes", IFERROR( INDEX(Source!$D$2:$D$6,MATCH($A2&$B2&$C2,Source!$A$2:$A$6&Source!$B$2:$B$6&Source!$C$2:$C$6,0)),"sta ff-not found"),"not staff")
committed with Ctrl-Shift-Enter.
Note the concatenation of the search criteria in the MATCH: MATCH($A2&$B2&$C2,Source!$A$2:$A$6&Source!$B$2:$B$6&Source!$C$2:$C$6,0)
If this doesn't help, maybe uploading a sample workbook would clarify what you require. It doesn't need to be the whole workbook, just a cut down version with names, etc., removed to protect the innocent.
Regards, TMS
Would it work with this?
If I'm reading the formula correctly, then I'd need to create another table that contains the data that I'm looking for? Similar to DGET or Match/Index?
Once I have the set values, then I would use the concatenation (&) to select multiple values for matching?
Sorry, if I seem dense, but I've been working at this all friggin day and my mind began snapping about 2 hours ago! It's getting a little hard to concentrate!!!!
Thanks though, I'll modify the formula to fit my worksheet. And see how it goes.
In the meantime, I've attached the worksheet with all of the sensitive data removed. I'd like to display the contents of column Q in the DoC Results worksheet if the formula result in 'Employee Listing'!I:I is >1
The rest is already pretty much public knowledge for those determined enough to find out what a couple of people in Fiji are attending! LOL.
Like I said, SNAP and now the verbal diarrohea begins.
Last edited by mrmetaldragon; 02-07-2012 at 05:08 AM. Reason: Missed information
The formula in column I should be:
=IFERROR(VLOOKUP(A2,'DoC Results'!$C$2:$AF$581,21,FALSE),"")
Note the absolute cell references.
To return the date, you could use:
=IF(H2="Completed",IFERROR(VLOOKUP(A2,'DoC Results'!$C$2:$AF$581,15,FALSE),""),"")
However, if the course has been taken more than once, it will return the first one it finds.
The easy way out of that dilemma is to sort the records by "newest to oldest" date within staff number.
There is, no doubt, a complex formula to return the newest date but it would take a while to research that ... and may not be necessary.
Hope this helps.
Regards, TMS
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!
Thanks for the rep.
With this new variation, you're back to an array function using INDEX and MATCH where you will need to concatenate the salary number and the course code as the search item.
However, I'm not sure where you get the course code from. It's not a field in the Employee Listing and you can't get it from the header because the header doesn't have the full course code. So, you could create the course code with a separate VLOOKUP to convert the heading to the relevant course code and then concatenate that with the Salary ID.
But, I'm going to leave that with you to play with. I suspect it's do-able but you'll need to build the (a) course lookup table.
Regards, TMS
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks