When I used it in a conditional format, there was a formula error, so I put
it in a cell to test - and did not Ctrl/shift/enter to make it an array
formula when I tested it.

Oops.

I'll see if I can sort this out tonight and post back later.

Thanks!

"T. Valko" wrote:

That gives a #VALUE error if the value in A2
does not match a value in the list.

Yes, if entered as a formula on the worksheet without array entering it.

I thought you wanted a conditional formatting formula:

to create a conditional format.


--
Biff
Microsoft Excel MVP


"mooresk257" wrote in message
...
That gives a #VALUE error if the value in A2 does not match a value in the
list.

I found this works for any value in the list "Department":

=(NOT(ISNA(MATCH(A2,Department,0))))

but I was hoping for something simpler.

"T. Valko" wrote:

Try it like this:

=OR(A2=Department)

--
Biff
Microsoft Excel MVP


"mooresk257" wrote in message
...
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!