Sep 15 2023 07:37 AM
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?
Sep 15 2023 07:56 AM
=IF(ISNUMBER(MATCH(A2,$F$2:$F$21,0)),"","not in List B")
Does this return the intended result?
Sep 16 2023 01:46 AM
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.
"Authorised"
= LET(
authorised, COUNTIFS(listB, listA),
FILTER(listA, authorised)
)
"No access"
= LET(
duplicated, VSTACK(listA, listB),
noAccess, UNIQUE(duplicated,,1),
noAccess
)
Both of these require 365.