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.

Note that I'm aware of the following formula but what I need is for the condition to accomodate the case where the cell value is not equal to, but instead merely includes, the text above:

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

Any help would be much appreciated.

Thank you.

-H

Try this:

=IF(AND(ISNUMBER(SEARCH("lewis",A1)),(ISNUMBER(SEARCH("CA",B1)))),"41",0)

Hi

Do you want this as part of an event that is based on a change to A1 or B1 ( or some other item(s)), or as run on demand code?

Note that a function cannot change itself from a function to a value. If you want to keep with a function, then you would have to specify somewhere else what should be in C1, and bring that in as the result.

rylo

Please read the Forum Rules about posting the same question in two forums.

Sorry. Is there some way I can delete the original and have it only appear in this forum?

rylo,

I'm running it on demand. What coticphreak said was helpful, except: I'm trying to copy the outputted value into the original column (C in the original question)--only the values that were the result of the conditions being true (i.e., the ones that have "41"). I tried to sort the spreadsheet by the new column (where I put the formula coticphreak suggested). But I got an error about "operation requires merged cells to be identically sized." Is there any way around this?

Originally Posted by harrydavid1

rylo,

I'm running it on demand. What coticphreak said was helpful, except: I'm trying to copy the outputted value into the original column (C in the original question)--only the values that were the result of the conditions being true (i.e., the ones that have "41"). I tried to sort the spreadsheet by the new column (where I put the formula coticphreak suggested). But I got an error about "operation requires merged cells to be identically sized." Is there any way around this?

an easy way to do this would be to add another column, make it hidden if you want and give it the same formula as you have in C1,so lets say u make it D1, then change the else part of the formula in C1 to D1, so:

=IF(AND(ISNUMBER(SEARCH("lewis",A1)),(ISNUMBER(SEARCH("CA",B1)))),"41",D1)

Thanks. And is there a way to choose over what range to paste the formula to (C1-C9000, e.g.), rather than dragging over page after page of the column? On the other hand, I don't want to paste to the whole column because of the time it takes the computer to do that.

Originally Posted by harrydavid1

Thanks. And is there a way to choose over what range to paste the formula to (C1-C9000, e.g.), rather than dragging over page after page of the column? On the other hand, I don't want to paste to the whole column because of the time it takes the computer to do that.

copy, hit the column you want to paste to, so if its C, hit C to select the whole thing, and paste. I just did it on my 2+ year old machine and it did it almost instantly. all the way to cell 65536 (the last possible cell)

Hm. Well for whatever reason it takes my (new) MacBook a couple minutes. Is there another way to tell it what paste range?