AND expressions with text and numbers

[CDandVinyl]

Hi

I'm trying to say that if H2 contains the letters CD and J2 is less than 35000 , then put "EX/EX" , otherwise out M2.

This is the formula I'm tying , but it's not working :

=IF(AND(H2="*CD*",J2<35000),"EX/EX",M2)


It doesn't give an error , but doesn't give correct outcomes either.

Can anyone suggest a solution? Grateful for your advice.


Best Wishes ,

[daffodil11]

Give this a shot:

=IF(AND(ISNUMBER(SEARCH("CD",H2)),J2<35000),"EX/EX",M2)

[Tony Valko]

Are you trying to use wildcards with the *CD* ?

If so, the AND function doesn't support the use of wildcards.

Try something like this...

=IF(AND(COUNT(SEARCH("CD",H2)),J2<35000),"EX/EX",M2)

[CDandVinyl]

Hi

Thanks for getting back.

No joy so far , I'm afraid. I think it may be a formatting issue , but can't be sure..

The formulas don't have an immediate effect on the existing cell content but do sometimes respond when I enter the data manually.

It's very puzzling. I can't see why they wouldn't work.

I've attached a sample of the file as it might clearer that way. Grateful if you have time.

Best Wishes ,

[daffodil11]

Looks good to me. Do the formulas work as I've inputted them?

[FDibbins]

If your sample answers are in column M, then using daffodil's formula gives you the exact same thing

[FDibbins]

The formulas don't have an immediate effect on the existing cell content

Do you mean other cells in your worksheet - of teh cells you copied the formulas to?

Formulas cannot affect other cells, they ONLY affect the cell they are in

[CDandVinyl]

Hi

Ok it's looking good now. Thanks for your help! I'm grateful.

Best Wishes ,

[daffodil11]

Happy times for everyone! Glad it all worked out!