I have a named list "Department" that is made variable by using this formula
in the "Refers to" box:

=OFFSET(Sheet1!$B$2,0,0,(COUNTA(Sheet1!$B$2:$B$16) ),1)

Say the list contains the following:

Dept 1
Dept 2
Dept 3

I want to create a logical test to determine if a value in a cell matches
one or more of the values in that list to create a conditional format. This
is easy enough to do by using an "OR" function to test the cell contents, for
example, to test if A2 = "Dept 2" or "Dept 3",

=IF(OR(A2="Dept 2",A2="Dept 3"),TRUE,FALSE)

But what I am having trouble with is finding a way to test if "Dept 2" or
"Dept 3" are contained in list "Department".

I'd like to use some sort of lookup test using the list name "Department"
rather than a range of cells.

Any suggestions on how to do this?

Thanks!