Excel Formula Needed

Copper Contributor

I was tasked at work to use a formula that will check from two separate name list names that are not on one list because we need to remove access to the people who are not on one of the list. I don't know what formula to use, can someone please help me? 

2 Replies


=IF(ISNUMBER(MATCH(A2,$F$2:$F$21,0)),"","not in List B")

Does this return the intended result?

excel formula.png 


As usual, there are multiple approaches available.  Also, one can either annotate the original list or use FILTER to separate authorised and no access into two lists.

= LET(
    authorised, COUNTIFS(listB, listA),
    FILTER(listA, authorised)
"No access"
= LET(
    duplicated, VSTACK(listA, listB),
    noAccess,   UNIQUE(duplicated,,1),

Both of these require 365.