I would add the =isnumber() check.

=OR(B2="Yes",isnumber(MATCH(A2,Department,0)))

(untested)


mooresk257 wrote:

This works fine, thanks.

The only problem with =MATCH(A2,Department,0) is that it returns #REF if
there is no data in a list, and #NA if there are no matching values. This
means that the =MATCH(A2,Department,0) formula cannot be used with an "AND"
or "OR" to test other cell conditions with or against a value in the list.

For example, say I want to apply a conditional format to a cell either if
there is a matching value from the list in one cell, or the contents of a
different cell is "Yes". If the list is empty or does not have a matching
value, and I try to use

=OR(B2="Yes",MATCH(A2,Department,0))

the result of the formula does not equate to true or false, so no format is
applied.

"T. Valko" wrote:

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

As a conditional formatting formula you even just use:

=MATCH(A2,Department,0)

When MATCH evaluates to a number the format will be applied. When MATCH
evaluates to #N/A the format will not be applied.

--
Biff
Microsoft Excel MVP


"Dave Peterson" wrote in message
...
You could use =isnumber() instead of =not(isna(...))


mooresk257 wrote:

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!






--

Dave Peterson




--

Dave Peterson