IF the first word of one cell is found within the contents of another...return value A

[jeffcoleky]

=IF(A2="","EMPTY",(IF(ISNUMBER(SEARCH(A2,B2)),"Match","No-Match")))

I currently use the above formula to find out if the contents of cell A2 are included in the cell B2. It works well when there is only 1 word in A2. When there is more than one, it tries to match the entire cell and I need it to only match the 1st.

How do I change this to search for only the FIRST word of A2 in cell B2, and return "Match" if it is found, and "No-Match" if not?

Your help is much appreciated.

 

[JackDanIce]

Does this work:

=IF(LEN(A2)<1,"EMPTY",(IF(ISNUMBER(SEARCH(LEFT(A2,FIND(" ",A2)-1),B2)),"Match","No-Match")))

 

[Sektor]

JackDanIce, I tried out formula with "ddd" in A and B - it says "No-Match".

 

[JackDanIce]

Good spot Sektor

My formula only works if A2 contains more than one word, seperated by a space. Otherwise, you'll need to build in an additional test for when A2 only contains a single word (or a revision of the formula itself)

 

[jeffcoleky]

Does this work:

=IF(LEN(A2)<1,"EMPTY",(IF(ISNUMBER(SEARCH(LEFT(A2,FIND(" ",A2)-1),B2)),"Match","No-Match")))

No, it doesn't for some reason. I wish I could tell you on which types of data it works which others don't, but i can't seem to see a pattern to give you.

Here's some sample data to use. Obviously the code changes slightly in this example. I would want to test to see if the FIRST WORD of Cell B2 is or is not included in cell D2.

<style type="text/css">
table.tableizer-table {border: 1px solid #CCC; font-family: Arial, Helvetica, sans-serif; font-size: 12px;} .tableizer-table td {padding: 4px; margin: 3px; border: 1px solid #ccc;}
.tableizer-table th {background-color: #104E8B; color: #FFF; font-weight: bold;}
</style>

<table class="tableizer-table">
<tr class="tableizer-firstrow"><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th></tr> <tr><td>1</td><td>First</td><td>Last</td><td>Parties</td><td>Match?</td></tr> <tr><td>2</td><td>Terry & Julia</td><td>Jones</td><td>JONES, TERRY, J; JONES, JULIA, A</td><td>No-Match</td></tr> <tr><td>3</td><td>Robert & Brenda</td><td>Smith</td><td>SMITH, BRENDA; SMITH, BRENDA, B; SMITH, ROBERT, G; METRO GOVERNMENT; CAPITAL SOURCE BANK; AEON FINANCIAL LLC</td><td>No-Match</td></tr> <tr><td>4</td><td>Barry</td><td>James</td><td>JAMES, BARRY, L; SEARS ROEBUCK & CO</td><td>Match</td></tr> <tr><td>5</td><td>Jacqueline Ebert & Shearice Ellis</td><td> </td><td>ELLIS, SHEARICE, E; EBERT, JACQUELINE; ELLIS, GERALD; YOUR COUNTY FEDERAL CREDI, T UNION</td><td>No-Match</td></tr> <tr><td>6</td><td>Rodney & Antonya</td><td>Daisey</td><td>DAISEY, RODNEY, T; DAISEY, ANTONYA, A; COUNTY ME, TRO GOVERNMENT</td><td>No-Match</td></tr> <tr><td>7</td><td>Toni</td><td>Young</td><td>YOUNG, TONI, S; YOUNG, TONI</td><td>Match</td></tr></table>

 

[CharlesChuckieCharles]

=IF(ISERR(FIND(UPPER(B2),UPPER(D2))),"No-Match","Match")

 

[CharlesChuckieCharles]

=IF(ISERR(FIND(UPPER(IF(ISERR(LEFT(B2,FIND(" ",B2))),B2LEFT(B2FIND(" ",B2))),UPPER(D2)),"No-Match","Match")

checks position of first space in b2, if no space returns the whole of b2 compares with d2

 

[T. Valko]

=IF(A2="","EMPTY",(IF(ISNUMBER(SEARCH(A2,B2)),"Match","No-Match")))

I currently use the above formula to find out if the contents of cell A2 are included in the cell B2. It works well when there is only 1 word in A2. When there is more than one, it tries to match the entire cell and I need it to only match the 1st.

How do I change this to search for only the FIRST word of A2 in cell B2, and return "Match" if it is found, and "No-Match" if not?

Your help is much appreciated.

Try this...

=IF(A2="","Empty",IF(COUNT(SEARCH(LEFT(A2,FIND(" ",A2&" ")-1),B2)),"Match","No Match"))

 

[jeffcoleky]

=IF(A2="","Empty",IF(COUNT(SEARCH(LEFT(A2,FIND(" ",A2&" ")-1),B2)),"Match","No Match"))

That one worked as far as i can tell! thanks T. Valko

(charleschuckiecharles & JackDanIce, thanks for trying too)

 

[jeffcoleky]

=IF(A2="","Empty",IF(COUNT(SEARCH(LEFT(A2,FIND(" ",A2&" ")-1),B2)),"Match","No Match"))

That one worked as far as i can tell! thanks T. Valko

(charleschuckiecharles & JackDanIce, thanks for trying too)

Not to put another question within this post, but would be easy to change this formula to search cell B2 for ANY word in cell A2, rather than just the first word of A2?