If cell b1,b2 or b3 is contained in cell a1, then enter the word that is contained in cell a1

[jeremypyle]

If I had 2 lists. List 1 was in column A and list 2 was column B :

A1) bigtomato
A2) fatamerican
A3) happysailer

B1) round
B2) hat
B3) big

And I want to have a code that says if A1 contains (B1,B2 or B3) then enter that contained word. In this case A1 contains the word "big" from B3

How would I code this:

I have tried created the following code in C1
=IF(OR((ISNUMBER(SEARCH($B1,$A1))),(ISNUMBER(SEARCH($B2,$A1))),(ISNUMBER(SEARCH($B3,$A1)))),$B1,"")

This code only works if the word "big" was in cell one

The code somehow needs to say if A1 contains B1,B2 or B3 then enter b1,b2 or b3 (depending on which word was contained)

If you can figure this out, you are a GENIUS!!!

 

[mikerickson]

You could put this CSE formula in C1 and drag it down. (I changed the B list a little.)

=INDEX($B:$B,MAX(IF(ISNUMBER(MATCH("*"&$B$1:$B$3&"*",A1,0)),ROW($B$1:$B$3),-1)),1)
This needs to be entered with Ctrl-Shift-Enter (Cmd+Return for Mac)

The #VALUE! in C3 is because none of the B words are in "happysailor".

<table border=1 cellspacing=0>
<tr align="center" bgcolor=#A0A0A0><td width=25> <td width=25><b>A</b><td width=25><b>B</b><td width=25><b>C</b></tr>
<tr><td align="center" bgcolor=#A0A0A0><b>1</b><td align="left" bgcolor=#FFFFFF>bigtomato<td align="left" bgcolor=#FFFFFF>round<td align="left" bgcolor=#FFFFFF>big</tr>
<tr><td align="center" bgcolor=#A0A0A0><b>2</b><td align="left" bgcolor=#FFFFFF>fatamerican<td align="left" bgcolor=#FFFFFF>tame<td align="left" bgcolor=#FFFFFF>tame</tr>
<tr><td align="center" bgcolor=#A0A0A0><b>3</b><td align="left" bgcolor=#FFFFFF>happysailer<td align="left" bgcolor=#FFFFFF>big<td align="left" bgcolor=#FFFFFF>#VALUE!</tr>
</table>

 

[jeremypyle]

thanks for your help. What do you mean though that "This needs to be entered with Ctrl-Shift-Enter"?

Can I not simply copy your copy and paste into C1?

How do I enter using Ctrl-shift-enter?

Thanks heaps

 

[jeremypyle]

Forget my last question. Thanks heaps. Wow, I can't believe that you knew how to do this. You truly are a GENIUS. thanks so much

 

[jeremypyle]

actually one last question:

Your formula grabs the first mention of a word.

What If I create a new column D and try to grab any other words from the B column. For example, I have added a new word tomato in B4. How do I make it so that D1 grabs the word tomato as in the picture shown

 

[mikerickson]

Using the formula from C1, except where C1's formula uses A1, D1's use SUBSTITUTE(A1,C1,"")

 

[jeremypyle]

sorry, I'm useless at excel

Do you mean this code in D1:

=INDEX($B:$B,MAX(IF(ISNUMBER(MATCH("*"&$B$1:$B$4&"*",SUBSTITUTE(A1,C1,""),0)),ROW($B$1:$B$4),-1)),1)

I have changed the "b3s" to "b4s" because of the extra word "ato" in b4

The code doesn't seem to work

If I have

a1)bigtomato
a2)fatamerican
a3)happysailor
a4)plant

then in column b

b1)round
b2)tame
b3)big
b4)tomato

and enter your original code into C1 (except changing both references of $B$3 to $B$4), then in c1 appears the word "ato"

So in cell d1, I want the word "big" to appear because the word "big" is also in column b

I tried the following code in d1:

=INDEX($B:$B,MAX(IF(ISNUMBER(MATCH("*"&$B$1:$B$4&"*",SUBSTITUTE(A1,C1,""),0)),ROW($B$1:$B$4),-1)),1)

 

[mikerickson]

This uses a helper column as well as a named range that evaluates to B1:B6

In D1, the CSE formula
=INDEX($B:$B,MAX(IF(ISNUMBER(MATCH("*"&NameList&"*",A1,0)),ROW(NameList),-1)),1

In E1,
=SUBSTITUTE(A1,IFERROR(D1,""),"",1)

In F1, the CSE formula
=INDEX($B:$B,MAX(IF(ISNUMBER(MATCH("*"&NameList&"*",E1,0)),ROW(NameList),-1)),1)

In G1,
=SUBSTITUTE(E1,IFERROR(F1,""),"",1)

In H1, the CSE formula
=INDEX($B:$B,MAX(IF(ISNUMBER(MATCH("*"&NameList&"*",G1,0)),ROW(NameList),-1)),1)

<table border=1 cellspacing=0>
<tr align="center" bgcolor=#A0A0A0><td width=25> <td width=25><b>A</b><td width=25><b>B</b><td width=25><b>C</b><td width=25><b>D</b><td width=25><b>E</b><td width=25><b>F</b><td width=25><b>G</b><td width=25><b>H</b></tr>
<tr><td align="center" bgcolor=#A0A0A0><b>1</b><td align="left" bgcolor=#FFFFFF>abigtomato<td align="left" bgcolor=#FFFFFF>round<td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#CCFFCC>tomato<td align="left" bgcolor=#FFFFFF>abig<td align="left" bgcolor=#CCFFCC>big<td align="left" bgcolor=#FFFFFF>a<td align="left" bgcolor=#CCFFCC>#VALUE!</tr>
<tr><td align="center" bgcolor=#A0A0A0><b>2</b><td align="left" bgcolor=#FFFFFF>fatamerican<td align="left" bgcolor=#FFFFFF>tame<td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#CCFFCC>can<td align="left" bgcolor=#FFFFFF>fatameri<td align="left" bgcolor=#CCFFCC>tame<td align="left" bgcolor=#FFFFFF>fari<td align="left" bgcolor=#CCFFCC>far</tr>
<tr><td align="center" bgcolor=#A0A0A0><b>3</b><td align="left" bgcolor=#FFFFFF>happysailor<td align="left" bgcolor=#FFFFFF>big<td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#CCFFCC>#VALUE!<td align="left" bgcolor=#FFFFFF>happysailor<td align="left" bgcolor=#CCFFCC>#VALUE!<td align="left" bgcolor=#FFFFFF>happysailor<td align="left" bgcolor=#CCFFCC>#VALUE!</tr>
<tr><td align="center" bgcolor=#A0A0A0><b>4</b><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF>tomato<td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF></tr>
<tr><td align="center" bgcolor=#A0A0A0><b>5</b><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF>can<td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF></tr>
<tr><td align="center" bgcolor=#A0A0A0><b>6</b><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF>far<td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF></tr>
<tr><td align="center" bgcolor=#A0A0A0><b>7</b><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF></tr>
</table>

 

[jeremypyle]

thanks, just realised you had answered this. I will try it and see what happens

 

[mikerickson]

I can't explain it not working for you, it works for me.
Are the formulas in D1, F1 and H1 being entered with Ctrl-Shift-Enter?

Also, if you look at the second row, I note that "far" is not a substring of "fatamerican", it is a artifact of the removal of another substring fatamerican.

If this is a problem for you, change the formula in E1 to =SUBSTITUTE(A1,IFERROR(D1,""),"~",1). Similarly for G1.