Check whether manually entered value is in an array

akparasite

Board Regular
Joined
Jul 15, 2002
Messages
55
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
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
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
 

Forum statistics

Threads
1,144,437
Messages
5,724,356
Members
422,546
Latest member
KevinOnTheDaily

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top