# Thread: search multiple criteria, return one value.

1. ## search multiple criteria, return one value.

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).
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. 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. Originally Posted by NBVC
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. 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. 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
•