+ Reply to Thread
Results 1 to 7 of 7

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

  1. #1
    Registered User
    Join Date
    01-31-2012
    Location
    Fiji Islands
    MS-Off Ver
    Excel 2007
    Posts
    6

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

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    38,069

    Re: Returning specific values based on multiple criteria in a tablewith duplicate ent

    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)),"staff-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
    Trevor Shuttleworth - Excel Aid

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Registered User
    Join Date
    01-31-2012
    Location
    Fiji Islands
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: Returning specific values based on multiple criteria in a tablewith duplicate ent

    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.
    Attached Files Attached Files
    Last edited by mrmetaldragon; 02-07-2012 at 06:08 AM. Reason: Missed information

  4. #4
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    38,069

    Re: Returning specific values based on multiple criteria in a tablewith duplicate ent

    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

  5. #5
    Registered User
    Join Date
    01-31-2012
    Location
    Fiji Islands
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: Returning specific values based on multiple criteria in a tablewith duplicate ent

    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!

  6. #6
    Registered User
    Join Date
    01-31-2012
    Location
    Fiji Islands
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: Returning specific values based on multiple criteria in a tablewith duplicate ent

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

  7. #7
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    38,069

    Re: Returning specific values based on multiple criteria in a tablewith duplicate ent

    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1