Excel formula if two cells have same word

S

shacol03

New Member
Joined
Oct 24, 2017
Messages
18
Office Version
  1. 365
Platform
  1. Windows
Hi,

Please help me, how to make a formula if two cells have multiple words and if they have same word will become true.
Example.

I want in Column C will "True" if Column A & B have same word.

[TABLE="width: 500"]
<tbody>[TR]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[/TR]
[TR]
[TD]Fc Innsbruck Union[/TD]
[TD]Fc Stated Innsbruck[/TD]
[TD]True[/TD]
[/TR]
[TR]
[TD]Fc Hoechst[/TD]
[TD]Blau Hoechst Feldkirch[/TD]
[TD]True[/TD]
[/TR]
[TR]
[TD]Al Hidd[/TD]
[TD]Qalali[/TD]
[TD]False[/TD]
[/TR]
[TR]
[TD]Dinamo Minsk[/TD]
[TD]Minsk Mozyr[/TD]
[TD]True[/TD]
[/TR]
</tbody>[/TABLE]

Thank you & Regards,
Roy
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
Sort by date Sort by votes
Welcome to the forum.

This kind of thing is never pretty using formulas. It's much easier to maintain with a VBA user-defined function. However, the following formula does seem to work:

ABC

<tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #FAFAFA"]Fc Innsbruck Union[/TD]
[TD="bgcolor: #FAFAFA"]Fc Stated Innsbruck[/TD]
[TD="align: right"]TRUE[/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: #FAFAFA"]Fc Hoechst[/TD]
[TD="bgcolor: #FAFAFA"]Blau Hoechst Feldkirch[/TD]
[TD="align: right"]TRUE[/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: #FAFAFA"]Al Hidd[/TD]
[TD="bgcolor: #FAFAFA"]Qalali[/TD]
[TD="align: right"]FALSE[/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: #FAFAFA"]Dinamo Minsk[/TD]
[TD="bgcolor: #FAFAFA"]Minsk Mozyr[/TD]
[TD="align: right"]TRUE[/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: #FAFAFA"]The Balloon Place[/TD]
[TD="bgcolor: #FAFAFA"]100 Ball Court[/TD]
[TD="align: right"]FALSE[/TD]

[TD="align: center"]6[/TD]
[TD="bgcolor: #FAFAFA"]100 Ball Court[/TD]
[TD="bgcolor: #FAFAFA"]The Balloon Place[/TD]
[TD="align: right"]FALSE[/TD]

[TD="align: center"]7[/TD]
[TD="bgcolor: #FAFAFA"]ABC Jack Street[/TD]
[TD="bgcolor: #FAFAFA"]Blackjack way[/TD]
[TD="align: right"]FALSE[/TD]

[TD="align: center"]8[/TD]
[TD="bgcolor: #FAFAFA"]Blackjack way[/TD]
[TD="bgcolor: #FAFAFA"]ABC Jack Street[/TD]
[TD="align: right"]FALSE[/TD]

[TD="align: center"]9[/TD]
[TD="bgcolor: #FAFAFA"]This is a very long line to test it[/TD]
[TD="bgcolor: #FAFAFA"]I don't know if every word here works very well[/TD]
[TD="align: right"]TRUE[/TD]

[TD="align: center"]10[/TD]
[TD="bgcolor: #FAFAFA"]This is a long line to test it[/TD]
[TD="bgcolor: #FAFAFA"]I don't know if every word here works very well[/TD]
[TD="align: right"]FALSE[/TD]

[TD="align: center"]11[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]TRUE[/TD]

</tbody>
Sheet5​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]C1[/TH]
[TD="align: left"]{=ISNUMBER(AGGREGATE(15,6,SEARCH(MID(" "&A1&" ",IF(MID(" "&A1&" ",ROW(INDIRECT("1:"&LEN(A1)+1)),1)=" ",ROW(INDIRECT("1:"&LEN(A1)+1)),NA()),FIND(" "," "&A1&" ",ROW(INDIRECT("2:"&LEN(A1)+2)))-ROW(INDIRECT("1:"&LEN(A1)+1))+1)," "&B1&" "),1))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]


Note 1: AGGREGATE was added in 2010, older versions of Excel can't handle this formula.

Note 2: When pasting your sample data, the spaces came across as character code 160. I don't know if that's how the data really is, or if it's just an artifact of the copy/pasting process. If the data actually has code 160 instead of spaces, change every instance of " " to CHAR(160) in the formula. Or use Replace All.
 
Last edited:
Upvote 0
Eric W said:
Welcome to the forum.

This kind of thing is never pretty using formulas. It's much easier to maintain with a VBA user-defined function. However, the following formula does seem to work:

ABC

<tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #FAFAFA"]Fc Innsbruck Union[/TD]
[TD="bgcolor: #FAFAFA"]Fc Stated Innsbruck[/TD]
[TD="align: right"]TRUE[/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: #FAFAFA"]Fc Hoechst[/TD]
[TD="bgcolor: #FAFAFA"]Blau Hoechst Feldkirch[/TD]
[TD="align: right"]TRUE[/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: #FAFAFA"]Al Hidd[/TD]
[TD="bgcolor: #FAFAFA"]Qalali[/TD]
[TD="align: right"]FALSE[/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: #FAFAFA"]Dinamo Minsk[/TD]
[TD="bgcolor: #FAFAFA"]Minsk Mozyr[/TD]
[TD="align: right"]TRUE[/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: #FAFAFA"]The Balloon Place[/TD]
[TD="bgcolor: #FAFAFA"]100 Ball Court[/TD]
[TD="align: right"]FALSE[/TD]

[TD="align: center"]6[/TD]
[TD="bgcolor: #FAFAFA"]100 Ball Court[/TD]
[TD="bgcolor: #FAFAFA"]The Balloon Place[/TD]
[TD="align: right"]FALSE[/TD]

[TD="align: center"]7[/TD]
[TD="bgcolor: #FAFAFA"]ABC Jack Street[/TD]
[TD="bgcolor: #FAFAFA"]Blackjack way[/TD]
[TD="align: right"]FALSE[/TD]

[TD="align: center"]8[/TD]
[TD="bgcolor: #FAFAFA"]Blackjack way[/TD]
[TD="bgcolor: #FAFAFA"]ABC Jack Street[/TD]
[TD="align: right"]FALSE[/TD]

[TD="align: center"]9[/TD]
[TD="bgcolor: #FAFAFA"]This is a very long line to test it[/TD]
[TD="bgcolor: #FAFAFA"]I don't know if every word here works very well[/TD]
[TD="align: right"]TRUE[/TD]

[TD="align: center"]10[/TD]
[TD="bgcolor: #FAFAFA"]This is a long line to test it[/TD]
[TD="bgcolor: #FAFAFA"]I don't know if every word here works very well[/TD]
[TD="align: right"]FALSE[/TD]

[TD="align: center"]11[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]TRUE[/TD]

</tbody>
Sheet5​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]C1[/TH]
[TD="align: left"]{=ISNUMBER(AGGREGATE(15,6,SEARCH(MID(" "&A1&" ",IF(MID(" "&A1&" ",ROW(INDIRECT("1:"&LEN(A1)+1)),1)=" ",ROW(INDIRECT("1:"&LEN(A1)+1)),NA()),FIND(" "," "&A1&" ",ROW(INDIRECT("2:"&LEN(A1)+2)))-ROW(INDIRECT("1:"&LEN(A1)+1))+1)," "&B1&" "),1))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]


Note 1: AGGREGATE was added in 2010, older versions of Excel can't handle this formula.

Note 2: When pasting your sample data, the spaces came across as character code 160. I don't know if that's how the data really is, or if it's just an artifact of the copy/pasting process. If the data actually has code 160 instead of spaces, change every instance of " " to CHAR(160) in the formula. Or use Replace All.
Click to expand...


Hi Sir Eric,

Thank you for the reply.

I'm sorry I have another problem. I want to apply in column (example below)

I want to put True in Column B, If Column A have same word in Column E within the column.

ABE

<tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #FAFAFA"]Fc Innsbruck Union[/TD]
[TD="bgcolor: #FAFAFA"]True[/TD]
[TD="align: right"]Blau Hoechst Feldkirch[/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: #FAFAFA"]Fc Hoechst [/TD]
[TD="bgcolor: #FAFAFA"]True[/TD]
[TD="align: right"]Fc Stated Innsbruck [/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: #FAFAFA"]Al Hidd[/TD]
[TD="bgcolor: #FAFAFA"]False[/TD]
[TD="align: right"] Minsk Mozyr[/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: #FAFAFA"]Dinamo Minsk [/TD]
[TD="bgcolor: #FAFAFA"]True[/TD]
[TD="align: right"]Qalali[/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: #FAFAFA"] The Balloon Place [/TD]
[TD="bgcolor: #FAFAFA"]True[/TD]
[TD="align: right"] The Balloon Place [/TD]

[TD="align: center"]6[/TD]
[TD="bgcolor: #FAFAFA"]100 Ball Court [/TD]
[TD="bgcolor: #FAFAFA"]True[/TD]
[TD="align: right"] Blackjack Ace[/TD]

[TD="align: center"]7[/TD]
[TD="bgcolor: #FAFAFA"]ABC Jack Street[/TD]
[TD="bgcolor: #FAFAFA"]False[/TD]
[TD="align: right"] Court B[/TD]

[TD="align: center"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="align: right"][/TD]

</tbody>
****** id="cke_pastebin" style="position: absolute; top: 0px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">
ABC

<tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #FAFAFA"]Fc Innsbruck Union[/TD]
[TD="bgcolor: #FAFAFA"]Fc Stated Innsbruck[/TD]
[TD="align: right"]TRUE[/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: #FAFAFA"]Fc Hoechst[/TD]
[TD="bgcolor: #FAFAFA"]Blau Hoechst Feldkirch[/TD]
[TD="align: right"]TRUE[/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: #FAFAFA"]Al Hidd[/TD]
[TD="bgcolor: #FAFAFA"]Qalali[/TD]
[TD="align: right"]FALSE[/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: #FAFAFA"]Dinamo Minsk[/TD]
[TD="bgcolor: #FAFAFA"]Minsk Mozyr[/TD]
[TD="align: right"]TRUE[/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: #FAFAFA"]The Balloon Place[/TD]
[TD="bgcolor: #FAFAFA"]100 Ball Court[/TD]
[TD="align: right"]FALSE[/TD]

[TD="align: center"]6[/TD]
[TD="bgcolor: #FAFAFA"]100 Ball Court[/TD]
[TD="bgcolor: #FAFAFA"]The Balloon Place[/TD]
[TD="align: right"]FALSE[/TD]

[TD="align: center"]7[/TD]
[TD="bgcolor: #FAFAFA"]ABC Jack Street[/TD]
[TD="bgcolor: #FAFAFA"]Blackjack way[/TD]
[TD="align: right"]FALSE[/TD]

[TD="align: center"]8[/TD]
[TD="bgcolor: #FAFAFA"]Blackjack way[/TD]
[TD="bgcolor: #FAFAFA"]ABC Jack Street[/TD]
[TD="align: right"]FALSE[/TD]

[TD="align: center"]9[/TD]
[TD="bgcolor: #FAFAFA"]This is a very long line to test it[/TD]
[TD="bgcolor: #FAFAFA"]I don't know if every word here works very well[/TD]
[TD="align: right"]TRUE[/TD]

[TD="align: center"]10[/TD]
[TD="bgcolor: #FAFAFA"]This is a long line to test it[/TD]
[TD="bgcolor: #FAFAFA"]I don't know if every word here works very well[/TD]
[TD="align: right"]FALSE[/TD]

[TD="align: center"]11[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]TRUE

[/TD]

</tbody>
</body>
Thanks & Regards

HUHUHUHU -_-
 
Upvote 0
Just put the formula in B1 instead of C1, and change the B1 near the end to a C1.
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question Question
Can't get MailMerge to work through VBA
Replies
0
Views
289
DetroitJimBurke
D
D
  • Question Question
Single (ARRAY?) formula so say TRUE or FALSE when all values in two similar ranges are the same
Replies
1
Views
357
Peter_SSs
Peter_SSs
R
  • Question Question
I can not use conditional formatting in Excel 2016 for Mac because the formula is too long
2
Replies
10
Views
1K
StephenCrump
S
M
  • Question Question
Formula for matching data in multiple cells and returning a true if they all match
Replies
2
Views
557
MichelleNeedsHelp
M
E
  • Solved
Filter and Textjoin formula if two columns match a text value in a cell - excel 365 in a Mac
Replies
7
Views
367
Fluff
Fluff

Forum statistics

Threads
1,224,828
Messages
6,181,217
Members
453,024
Latest member
Wingit77

Share this page

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
Back
Top