Logical Test for a value in a named list?

[mooresk257]

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!

 

[T. Valko]

Try it like this:

=OR(A2=Department)

 

[JH]

You need to enter T. Valko 's formula under conditional format for the cell
you want to test, it should works. Your original question refers to
conditional formating unless it is something else you want.


JH

 

[T. Valko]

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.

 

[Dave Peterson]

You could use =isnumber() instead of =not(isna(...))

 

[mooresk257]

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]

=(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.

 

[Dave Peterson]

I think I'd lose something in understanding what's supposed to happen.

(Sometimes I do things that make it easier for my sake--not necessarily for
excel's sake.)

 

[Ron Rosenfeld]

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!

=NOT(ISNA(VLOOKUP(A2,Department,1,FALSE)))

returns TRUE if A2 is found in Department; otherwise returns FALSE. So can be
used as a conditional formatting formula.
--ron

 

[mooresk257]

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.

=(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.

 

[Dave Peterson]

I would add the =isnumber() check.

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

(untested)

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.