If column A contains X then insert Y in column B

**Staylatess** · 06-23-2013, 07:10 PM

I have no idea how to make this work for my spreadsheet. I thought there was some easy way to just use options to tell Excel that this is what I want, but apparently I have to right my own rule and I just can't figure out what needs to be done. Please help, thank you! Again, what I am looking for is: If column A contains X then insert Y in column B

**FDibbins** · 06-23-2013, 07:31 PM

Hi and welcome to the forum

You may need to be a bit more specific than that?

Do you want the entire column B to show "Y" if there is an "X" anywhere in column A?
Do you want a CELL in column B to show "Y" if the corresponding CELL in column A contains "X"?

For the 2nd option, in B2 copied down as far as you need, use...
=if(A2="X","Y","") You didnt say what you wanted to see if it did not contain "X"

**Staylatess** · 06-23-2013, 07:49 PM

Yes the second option. So I will have a various words in column A. So if i type the word dog anywhere in column A, i want canine to auto fill into the cell directly to the right of it in column B. I will have several situations.....cat in column A needing feline in the cell directly to the right of that in column B. I am not sure if I insert this as a rule or if it is a script as well. It has been several years since I have done any work with excel.

**FDibbins** · 06-23-2013, 08:01 PM

If you have several words you want to "replace", then I would suggest you use vlookup() instead of IF().

Create a table with all the words you want to replace, andthen next to them, all the words you want to replace them with.
Cat...feline
Dog...Canine
etc
You can make this list in the same worksheet, or on another worksheet. Assume you create your table in D1:E10 (you can obviously increase this, just adjust teh rane accordingly in the formula)

in B2, copied down (Assuming A1 and B1 contain headings)...

=vlookup(B2,$D$1:$E$10,2,0)

What this means is...
look for the data in B2, in the table range D1:E10, if you find it, give me the corresponing value in the 2nd column, the 0 tells excel you want an exact match. the $ in the formula absolute (or fix) the cell references so they dont change when copied

You didnt say what you wanted to do if the data in A did not contain the words you are looking toi change?
If you want it to just give you the data in A, then use this insteead...
=iferror(vlookup(B2,$D$1:$E$10,2,0),A2)

**Staylatess** · 06-23-2013, 08:08 PM

I do not want to replace anything. I want the word canine to appear in the b column next to the word dog that appears in the a column. So, if dog is in cells a1, a7 and a13, I want canine to be inserted into b1, b7 and b13. Likewise with cat, if it is in a2, a5 and a12, I want b2, b5 and b12 to show feline.

**Staylatess** · 06-23-2013, 08:10 PM

In addition, there will be a rule or whatever it is called for everything in column A so that nothing in column A is without knowing what to put in column B.

**FDibbins** · 06-23-2013, 08:13 PM

Originally Posted by Staylatess

In addition, there will be a rule or whatever it is called for everything in column A so that nothing in column A is without knowing what to put in column B.

Do you mean that all entries in A will result in an answer in B? If so then just use my 1st suggestion...
=vlookup(B2,$D$1:$E$10,2,0)

**Staylatess** · 06-23-2013, 08:20 PM

So when making the list of words, would I put exactly dog...canine in cell D1? etc? Or do I put dog in D1 and canine in E1?

**Staylatess** · 06-23-2013, 08:26 PM

Sorry to be annoying, but I am wondering if this works if I say I want it to look in the entire column of A for that certain word and if it finds it to put the corresponding word into column B?