# Need help on a formula

#### Lana_S

##### New Member

Let’s say we have text in all of the following cells (but in cell B1 we only have one word) :
A1 B1 C1
A2
A3
A4
A5

I would like to know what’s the formula to find :

If the word in cell B1 appears in one of the cell of A1:A5 (but each cell from A1 to A5 contain more than one word) then if it’s true = C1

I hope it's clear enough

### Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
This seems a little clumsy to me, but it works in the limited situation you describe. I insert all of the blanks (" ") to avoid the problem of finding a word in cell B1 that is contained within a different word in one of the cells in A1:A5 (e.g. Cell B1 contains "here" and one of the words in A1:A5 is "there").

=IF(ISERR(FIND(" "&B1&" "," "&A2&" "&A3&" "&A4&" "&A5&" ")),"No Match",C1)

Thanks for your reply. But when I tried the formula in an excel sheet, it says there is an error in the formula. It's really weird because I tried a similar formula on a co-worker's computer and it worked but when I typed the exactly same formula in the same file on my computer it didn't worked, it said also there was an error in the formula. I reallly don't understand why.

Lana_S said:

Let’s say we have text in all of the following cells (but in cell B1 we only have one word) :
A1 B1 C1
A2
A3
A4
A5

I would like to know what’s the formula to find :

If the word in cell B1 appears in one of the cell of A1:A5 (but each cell from A1 to A5 contain more than one word) then if it’s true = C1

I hope it's clear enough

Putting a small sample would have been helpful...

Given A1:A5:

olga
olga singing
fred is fishing
home
theater

B1:

olga

What would be the result in C1?

Yeah, you're right, so here is a sample :
<table border>
<tr> <th> A1 :A200</th> <th> B1</th><th>C1</th><th>D1</th> </tr>
<tr> <td> fred is fishing </td> <td> olga </td><td> ppp </td><td> FORMULA?</td> </tr>
<tr> <td> olga singing </td> <td> ...</td><td> ...</td> </tr>
<tr> <td> home cooking </td> <td> .... </td> <td> ...</td></tr>
<tr> <td> house sea </td> <td> .... </td><td> ...</td> </tr>
<tr> <td>.... and so on.... </td></tr>
</table>

Here is the FORMULA I'm looking for:
If the word in cell B1 appears in one of the cells A1:A200 then I want the content of cell C1 to appear in D1. (In the example above 'ppp' would appear in D1 since the word 'olga' appears in cell A2)
And then I want to be able to extend the formula for all the words of column B.

Does such a formula exist?

Lana_S said:
Yeah, you're right, so here is a sample :
<table border>
<tr> <th> A1 :A200</th> <th> B1</th><th>C1</th><th>D1</th> </tr>
<tr> <td> fred is fishing </td> <td> olga </td><td> ppp </td><td> FORMULA?</td> </tr>
<tr> <td> olga singing </td> <td> ...</td><td> ...</td> </tr>
<tr> <td> home cooking </td> <td> .... </td> <td> ...</td></tr>
<tr> <td> house sea </td> <td> .... </td><td> ...</td> </tr>
<tr> <td>.... and so on.... </td></tr>
</table>

Here is the FORMULA I'm looking for:
If the word in cell B1 appears in one of the cells A1:A200 then I want the content of cell C1 to appear in D1. (In the example above 'ppp' would appear in D1 since the word 'olga' appears in cell A2)
And then I want to be able to extend the formula for all the words of column B.

Does such a formula exist?

=IF(ISNUMBER(MATCH("*"&B1&"*",\$A\$1:\$A\$200,0)),C1,"")

Thanks a lot. But once again I tried the formula in an excel sheet and it says there is an error in the formula...I don't know how come.

Lana_S said:
Thanks a lot. But once again I tried the formula in an excel sheet and it says there is an error in the formula...I don't know how come.
Book7
ABCD
1fred is fishingolgapppppp
2olga singingfishingqqqqqq
3home cookingfredyyyyyy
4
Sheet1

D1, copied down:

=IF(ISNUMBER(MATCH("*"&B1&"*",\$A\$1:\$A\$200,0)),C1,"")

Ok, I did exactly the same thing as you did and it still says there is an error in the formula, I'm going crazy. I write the same thing in the same cells as you, I copy and paste your formula and then when I click on enter, the error message appears.
At work the same thing happened, I did the same kind of formula on a computer and it worked but when I tried to do the exactly same formula in the same file on another computer it didn't work.
Anyway, I'll try on the computer where it worked, should work; Thanks a lot for your help.

Replies
0
Views
149
Replies
11
Views
329
Replies
2
Views
101
Replies
4
Views
669
Replies
4
Views
200

1,214,255
Messages
6,118,515
Members
448,830
Latest member
Drew Terp

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

### Which adblocker are you using?

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

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