Need help on a formula

Lana_S

New Member
Joined
Nov 17, 2005
Messages
8
Hi, could you please help me I need to find a formula :

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

Thanks in advance
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

Artemus

Board Regular
Joined
Dec 30, 2004
Messages
92
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)
 

Lana_S

New Member
Joined
Nov 17, 2005
Messages
8
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.
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,210
Lana_S said:
Hi, could you please help me I need to find a formula :

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

Thanks in advance

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?
 

Lana_S

New Member
Joined
Nov 17, 2005
Messages
8

ADVERTISEMENT

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?
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,210
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,"")
 

Lana_S

New Member
Joined
Nov 17, 2005
Messages
8

ADVERTISEMENT

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.
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,210
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,"")
 

Lana_S

New Member
Joined
Nov 17, 2005
Messages
8
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. :oops: 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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,002
Messages
5,834,821
Members
430,324
Latest member
bosphoruskid

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?

Disable AdBlock

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
Top