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

[japturtle]

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>

 

[bbott]

In C1:

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

 

[japturtle]

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>

 

[bbott]

Try this:

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

 

[japturtle]

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...

 

[bbott]

=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.)?

 

[shobolos]

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

 

[japturtle]

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.

 

[japturtle]

=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.

 

[jake32008]

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>