Thread: Populate Cell based on IF SEARCH() criteria in Different Cell

First off, apologies if this is already a solved issue somewhere on the forum. This is my first time using the forum and I have not had luck finding this issue addressed in my search of it so far.

My question is how to populate a cell if a value of another cell contains a specific text, and, if it does not, I want to populate a cell X columns from the one that contains the if statement.


The logic i'm trying to capture is:

if Col1R1 contains "J", then Col2R2=Col1R1, else Col4R2=Col1R1

I've tried using offset(), but have not had luck.

When I use if(search("Text",CellX,1),CellX,CellY=CellX) It does not recognize the else statement. I feel this should work (could be wrong) but I'm not able to solve thus far.

Again apologies if there is a previous thread regarding this. Haven't been able to find it.

if Col1R1 contains "J", then Col2R2=Col1R1, else Col4R2=Col1R1


You need two IF Statements, one in Col2R2 and one in Col4R2

B2 - Col2R2: =IF(A1="J", A1,"")
D2 - Col4R2: =IF(A1<>"J",A1,"")


Regards

Originally Posted by Human

...currently I have in cell H8 =if(search("X",B3,1), B3, N8=B3)

...I've never tried to define a cell value in an if statement like N8=B3, so I'm probably making an ignorant error there in the first place.

Expressions 101: A formula returns a single value, which is displayed in the cell containing the formula. A formula cannot cause a value to appear in some other cell. It look like you are trying to say that when the IF condition is FALSE, then put the value from B3 into N8. But what Excel does is to evaluate the expression "N8=B3", which it does by comparing the values of the two cells. This is a comparison, not an assignment. The two cells are not equal, so the result is FALSE.

If you want a value in one cell under one condition, and a value in a different cell under a different condition, then you need a formula in each of the target cells, as shown by TMShucks.

Right, it's still an evaluation and boolean.

You may wish to mark this thread as Solved. I guess it's not the answer you want but it's the best you're going to get.

Regards

Yes, sorry ... just me not noticing

just making sure.