# If cell A contains data from cell B then enter cell B in cell C

#### japturtle

##### New Member
If cell A contains data from cell B then enter cell B into cell C. Please help!

 A B C Planet Hollywood Hollywood Hollywood Amazon.com Amazon Amazon Home Depot 1234 Home Depot Home Depot

<tbody>
</tbody>

### Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
In C1:

=IF(COUNTIF(A1,"*"&B1&"*"),B1,"")

Thanks bbott. It didn't work for me though... Here is an actual example from my sheet. Also, I'm sorry! But I meant to say "cell A contains data from column B then enter data from column B in cell C."

 A B C SWEIS, INC. IREDALE SWEIS OFFICE SOLUTIONS OFFICE OFFICE RODIAL COMPANIES RODIAL RODIAL IREDALE MINERAL SWEIS IREDALE

<tbody>
</tbody>

Try this:

=IF(COUNTIF(A:A,"*"&B1&"*"),B1,"")

That didn't work :/

I changed it up a bit and entered =IF(COUNTIF(F1,"*"&G:G&"*"),G:G,"") but that resulted in c2 and c3 coming up correctly and nothing in c1 and c4...

=IF(COUNTIF(F1,"*"&G:G&"*"),G:G,"")

You can't return an entire column. Are you always just returning the first word of column A (i.e. Sweis, Office, etc.)?

=lookup(9.99999999999999e+307,find(\$b\$1:\$b\$4,a1)*row(\$b\$1:\$b\$4),\$b\$1:\$b\$4)

Last edited:
You can't return an entire column. Are you always just returning the first word of column A (i.e. Sweis, Office, etc.)?

I'd like to return the word(s) that matches in both columns.

=lookup(9.99999999999999e+307,find(\$b\$1:\$b\$4,a1)*row(\$b\$1:\$b\$4),\$b\$1:\$b\$4)

This worked! However, only in this example. Do you know why it wouldn't work once plugged into my sheet? My actual sheet has 1500+ rows in column A.

This array formula might help

{=IF(ISNUMBER(SEARCH(B\$2:B\$5,A2)),B\$2:B\$5,"")}

It seems to work but only returns the first value, but it's a start if anybody can figure out how to make the rest of it work

The result I got when entered into column C is

 A B C SWEIS, INC IREDALE OFFICE SOLUTIONS OFFICE RODIAL COMPANIES RODIAL IREDALE MINERAL SWEIS IREDALE

<tbody>
</tbody>

Last edited:

Replies
3
Views
113
Replies
1
Views
171
Replies
4
Views
164
Replies
7
Views
385
Replies
1
Views
331

1,203,103
Messages
6,053,539
Members
444,670
Latest member
laurenmjones1111

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back