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
 

Some videos you may like

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

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

Watch MrExcel Video

Forum statistics

Threads
1,118,436
Messages
5,572,099
Members
412,441
Latest member
kelethymos
Top