More than 30 logical expressions in AND -- Quandry

[sunny316]

Hello,

I have a formula which goes something like this

=IF(D7="Yes/No?","Select Yes or No on Instruction sheet", IF((D7="Yes") =AND(A18="N",A19="N",A20="N",A21="N",A22="N",A23="N",A24="N",A25="N",A26="N",A27="N",A28="N",A29="N",A30="N",A31="N",A32="N",A33="N",A34="N",AND(A35="N",A36="N",A37="N",A38="N",A39="N",A40="N",A41="N",A42="N", A43="N",A44="N",A45="N",A46="N",A47="N",A48="N",A49="N",A50="N",A51="N")),"ERROR: NOT identified !", ""))

now I wanted to expand it to

=IF(D7="Yes/No?","Select Yes or No on Instruction sheet", IF((D7="Yes") =AND(A18="N",A19="N",A20="N",A21="N",A22="N",A23="N",A24="N",A25="N",A26="N",A27="N",A28="N",A29="N",A30="N",A31="N",A32="N",A33="N",A34="N",A35="N",A36="N",A37="N",A38="N",A39="N",A40="N",AND(A41="N",A42="N",A43="N",A44="N",A45="N",A46="N",A47="N",A48="N",A49="N",A50="N",A51="N",A52="N",A53="N",A54="N",A55="N",A56="N",A57="N",A58="N",A59="N",A60="N")),"ERROR: Prohibited substance mismatch or NOT identified !", ""))

and it didn't work, says that formula is too long. I tried it this way

=IF(D7="Yes/No?","Select Yes or No on Instruction sheet", IF((D7="Yes") =AND(A18="N",A19="N",A20="N",A21="N",A22="N",A23="N",A24="N",A25="N",A26="N",A27="N",A28="N",A29="N",A30="N",A31="N",A32="N",A33="N",A34="N",A35="N",A36="N",A37="N",A38="N",A39="N",A40="N")=AND(A41="N",A42="N",A43="N",A44="N",A45="N",A46="N",A47="N",A48="N",A49="N",A50="N",A51="N",A52="N",A53="N",A54="N",A55="N",A56="N",A57="N",A58="N",A59="N",A60="N"),"ERROR: Prohibited substance mismatch or NOT identified !", ""))

but that didn't give me the expected result.

I'm using Office 2003, please help me with getting around the stupid limitation that Microsoft placed on using just 30 expressions in a function in MS Excel 2003.

Thanks..appreciate any help.

 

[freeisgood]

Hello,

I am using 2 tables and one of them has more than thirty options/logicals (40 to be exact).

=if(and(a1=code_table,a2=status_table),"Urgent","Normal")

Please help me figure out why it is not working?

 

[Aladin Akyurek]

Hello,

I am using 2 tables and one of them has more than thirty options/logicals (40 to be exact).

=if(and(a1=code_table,a2=status_table),"Urgent","Normal")

Please help me figure out why it is not working?

Care to describe in words what you want to achieve?

 

[freeisgood]

I have 2 columns, first one has a code (1a, 1b, 1c,..., 2a, 2b, 2c,...) 50 in total, but only 40 of them I want to flag. On the second column has one of the following options (Prospect, Applied, Under Review, Processing, Denied), but I only to flag the following: Applied, Under Review and Processing. I created 2 tables; code_table that has 40 codes and the other status_table that contains; Applied, Under Review and Processing. If the code in column #1 match with code_table and status in column #2 match with status_table, I want state in column #3 "Urgent", else state "Normal". Is this possible? [FONT=&quot]"and" and "or" functions are limited to 30 logical, is there a way around?[/FONT] Thanks for your help!

 

[Aladin Akyurek]

I have 2 columns, first one has a code (1a, 1b, 1c,..., 2a, 2b, 2c,...) 50 in total, but only 40 of them I want to flag. On the second column has one of the following options (Prospect, Applied, Under Review, Processing, Denied), but I only to flag the following: Applied, Under Review and Processing. I created 2 tables; code_table that has 40 codes and the other status_table that contains; Applied, Under Review and Processing. If the code in column #1 match with code_table and status in column #2 match with status_table, I want state in column #3 "Urgent", else state "Normal". Is this possible? [FONT=&quot]"and" and "or" functions are limited to 30 logical, is there a way around?[/FONT] Thanks for your help!

Is this...

C2:

=IF(ISNUMBER(MATCH(A2,code_table,0)),
    IF(ISNUMBER(MATCH(B2,status_table,0)),"Urgent","Normal")

what you require?

 

[freeisgood]

Yeeeessss!!!!! It’s working! Thank you so much! Now I need to understand the purpose of the function “ISNUMBER” in your formulation. Again thank you!

 

[Aladin Akyurek]

Yeeeessss!!!!! It’s working! Thank you so much! Now I need to understand the purpose of the function “ISNUMBER” in your formulation. Again thank you!

MATCH always returns a number or #N/A. ISNUMBER is used to ascertain that MATCH is returning a number, that is, a hit.