I think my brain is getting tangled in the if/vlookup values. How would I get the values True or False for H3:H6 for example? I have a list of users (col A) with roles (Col b) and course requirements for the roles (col c). I need to know if the user has completed all courses for their specified roles not just if the user has completed all courses. I have the True/False values for what courses they have completed. i.e. Has User 1 with Role 1 completed all Role 1 courses assigned to them.

How would I do this?

CodePudding user response：

You can use IfError for this if you change the courses to columns, then have it return a value for each. Since it will through an error if one course is blank then it will error out. =IFERROR(VLOOKUP(F3,A:D,3,FALSE),IFERROR(VLOOKUP(F3,A:D,2,FALSE),"Courses Incomplete"))

CodePudding user response：

Answer

The following formula, when placed into cell H3 and autofilled into the cells below, should produce the desired result.

=NOT(OR(COUNTIFS($A$3:$A,F3,$B$3:$B,G3),COUNTIFS($A$3:$A,F3,$B$3:$B,G3,$D$3:$D,FALSE)))

Explanation

The first COUNTIFS checks whether there is at least one row in the dataset that has the same User and Role as is specified in columns F and G. The second COUNTIFS checks whether there are any matching columns where at least one FALSE appears in column D (indicating that a course is incomplete).

Using these two conditions, the desired truth table is as follows:

* Note that this scenario is impossible.

This truth table matches with the truth table for a NOR gate. Wrapping the COUNTIF functions in a NOR statement (combination of the NOT function and OR function) creates the desired result: only when there is a matching column but there isn't an incomplete course does the function return the value of TRUE.

A screenshot of the final result (in Google Sheets, but everything should be identical)

Functions Used:

• NOT
• OR
• COUNTIFS