Check whether manually entered value is in an array

[akparasite]

I've got a conditional format condition formula that checks whether a manually entered combination of values is present within an array. There are three fields that are entered, and the values are compared against an array of existing values in their concatenated form.

For some reason it doesn't work! Kudos to anyone who can set me straight.

Here is my formula:


=NOT(ISNUMBER(MATCH(CONCATENATE($A1,$B1,$C1),CONC123,0))))

where,

1 - CONC123 is the array of existing values, concatenated; and,
2 - Columns A,B, and C values are the manual input cells.
3 - CONC123 is automatically sorted alphabetically each time the application is opened.

***edit*** I should add that I believe the reason it's not working is because I have the function nested within an OR statement where two other conditions are evaluated, like the following:

=OR($D="YES",$E=YES,NOT(ISNUMBER(MATCH(CONCATENATE($A1,$B1,$C1),CONC123,0))))

On its own it seems to work...what is the logic problem here?
This message was edited by akparasite on 2002-09-25 15:15

 

[PaddyD]

If it's working on its own, might it be the other element of the OR? Have you checked them on their own? If the formula you posted was cut from excel, there are a few obvious things missing - quotes round the second YES, full cell / column references - $D is not a valid reference.

How about describing what you want the CF to do...

Paddy