Excel Formula to List all not matching values between two named ranges based on criteria

Question

i need your help to achive a list of all none-matching values between two different ranges (actually, in different sheets too, but since i'm using named ranges, shoudn't be a problem).

This is the first range and it's values:

It contains the names of certain individuals who had been evaluated at work. Note that the values start on cell A3, since A1 and A2 contains header values.

Next range:

On Column A you'll find a list of all Executives that are or have been on the company. On Column B are the exit dates of theese people. If they're still working, it'll say "On Duty".

What i want is a Formula to list all people from the "Executives Name List" that are "On Duty" but not among the "Evaluated Name" list.

This are the named ranges that i use and the expected output:

Thanks!!!

Answer 1

Use this Array Formula:

=IFERROR(INDEX(executives,MATCH(1,(COUNTIF($I$2:I2,executives)=0)*(exit_date="On Duty")*(ISERROR(MATCH(executives,evaluated_name,0))),0)),"")

The output list must be in the third row. Change the I to refer to the column into which you want the output. If a different starting row is wanted then change the 2 to the row above the first formula, even if it is the title row.

Being an Array formula it needs to be confirmed with Ctrl-Shift-Enter instead of Enter when exiting Edit mode. If done correctly then Excel will put {} around the formula.

Then copy down till you get blanks.