IF(AND)) function: condition is part of another cell's value

Hi,

I'm trying to do a fancy find-and-replace, basically. I want something that does the following:

If A1 contains "Lewis" and B1 contains "CA" then change C1 to "41" else leave C1 the same as it was.

Any help would be much appreciated.

Thank you.

-H

Here are a few options:
1. The easiest is to put this in D1:
=IF(AND(A1="Lewis",B1="CA"),"41",C1).
2. If the formula must be in C1, then the formula has to include the value already in C1.
3. If non of the above is good enough, ask for help in the programming forum
//Ola

Hi there,

A non-macro solution would be to put the following formula into the first cell of a column that isn't part of your dataset (say D1) and copy down as required...

=IF(AND(A1="Lewis",B1="CA"),41,C1)

...and then convert the formula range (or the entire column) to values and move / cut it (Ctrl + X) over Column C (Ctrl + V).

If you do require a macro, running the following on the tab where the data resides should suffice:

Please Login or Register  to view this content.

HTH

Robert