Results 1 to 5 of 5

Thread: search multiple criteria, return one value.

  1. #1

    search multiple criteria, return one value.



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hello. I have two sheets; sheet1 is a report based on raw data from sheet2.
    On sheet2, column 1 is employee name, column 3 is name of course, column 6 is completion status.
    Sheet1 is a summary of each employee's completion status. I'd like to run a formula that can do this automatically. I've tried Sumproduct, Vlookup, Index/Match, and IF, but the wildcard is messing me up. Could someone please take a look?
    I need a formula that can cross reference the employee name and course on sheet1 with the raw data on sheet2 to determine if the employee has completed the training. The problem is the courses on sheet2 have multiple names for the same course. For example; Check-in, xxCheck-In, Check-In (7-14).
    Please see attachment for clarification.
    On sheet1, cell F5 would have a formula that would result in the text "Done", as this employee has completed the course. See sheet2 row five.

    Thank you!
    Test SCNG.xlsx

  2. #2
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,518
    Articles
    0
    Excel Version
    Excel 2016
    Do you mean this?

    in F5 enter formula:

    =IF(COUNTIFS(Sheet2!$A:$A,$C5,Sheet2!$C:$C,"*"&F$2&"*",Sheet2!$F:$F,"Completed")>0,"Done","")

    copied across and down.


  3. #3
    Quote Originally Posted by NBVC View Post
    Do you mean this?

    in F5 enter formula:

    =IF(COUNTIFS(Sheet2!$A:$A,$C5,Sheet2!$C:$C,"*"&F$2&"*",Sheet2!$F:$F,"Completed")>0,"Done","")

    copied across and down.
    That is absolutely perfect! I didn't consider countif, thank you very much!!

  4. #4
    ooops, the formula works great, but when I drag it down it changes the fill of the blank cells. Anyway to keep the unchanged cells like they were before?

  5. #5
    Problem solved with conditional formatting...

Posting Permissions

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