I think I may be asking alot here, but here it goes. I need a formula
that will return 'true' if ANY of the words in one column are in the
second column. I plan on using this 'true' value in an 'if' function.

For example (the formula would be in column C):

Column A Column B Column C
Wile E. Coyote coyote true
Wile E. Coyote Wile true

I hope my example is clear. Can someone provide some guidance?

TIA

On Apr 15, 11:19*am, bird_...@my-deja.com wrote:
> I think I may be asking alot here, but here it goes. *I need a formula
> that will return 'true' if ANY of the words in one column are in the
> second column. *I plan on using this 'true' value in an 'if' function.
>
> For example (the formula would be in column C):
>
> Column A * * * * * * * Column B * * * * *Column C
> Wile E. Coyote * * * * coyote * * * * * * * *true
> Wile E. Coyote * * * * *Wile * * * * * * * * *true
>
> I hope my example is clear. * Can someone provide some guidance?
>
> TIA

I need to shift gears here but unfortunately I can't edit my post. I
want something like a 'vlookup' that matches any of the words in
column A with column B and returns column B. Something like this:

=vlookup('any word in column A',column B,1,false)

On Apr 15, 3:53*pm, Ron Rosenfeld <r...@nospam.net> wrote:
> On Fri, 15 Apr 2011 09:45:08 -0700 (PDT), bird_...@my-deja.com wrote:
> >On Apr 15, 11:19 am, bird_...@my-deja.com wrote:
> >> I think I may be asking alot here, but here it goes. I need a formula
> >> that will return 'true' if ANY of the words in one column are in the
> >> second column. I plan on using this 'true' value in an 'if' function.
>
> >> For example (the formula would be in column C):
>
> >> Column A Column B Column C
> >> Wile E. Coyote coyote true
> >> Wile E. Coyote Wile true
>
> >> I hope my example is clear. Can someone provide some guidance?
>
> >> TIA
>
> >I need to shift gears here but unfortunately I can't edit my post. * I
> >want something like a 'vlookup' that matches any of the words in
> >column A with column B and returns column B. *Something like this:
>
> >=vlookup('any word in column A',column B,1,false)
>
> It is not clear what you want to match. *Claus's response will return True if any of the words in A1 are found in B1 and can be easily modified toreturn the word, or FALSE if the word is not there. *Something like:
>
> =if(ISNUMBER(SEARCH(B1,A1)),B1)
>
> If you want to know if ANY word in column B (all the cells) matches ANY word in column A (all the cells), you will have multiple matches. *How do you want to handle that?
>
> Perhaps you only want to compare the phrase in A1 with a list of words incolumn B, returning either the word in column B, or FALSE? *If that is the case, it makes more sense, but you still have to decide what you want todo in the event that multiple words in the list in column B are included in the Phrase in A1.

I just want to compare the phrase in one cell (A1 for example) to a
range of cells in column B (for example B1:B10) and wherever the match
is found return the text in the B cell. With the list I am using
there shouldn't be any duplicates.

For example

Cell A1 Column B Cell C1
(has formula)
Wile E. Coyote apple coyote
(the match was found in B5 and I want the formula to put the text in
the match cell here)
blue
cherry
gun
coyote

On Apr 15, 6:55*pm, bird_...@my-deja.com wrote:
> On Apr 15, 3:53*pm, Ron Rosenfeld <r...@nospam.net> wrote:
>
>
>
> > On Fri, 15 Apr 2011 09:45:08 -0700 (PDT), bird_...@my-deja.com wrote:
> > >On Apr 15, 11:19 am, bird_...@my-deja.com wrote:
> > >> I think I may be asking alot here, but here it goes. I need a formula
> > >> that will return 'true' if ANY of the words in one column are in the
> > >> second column. I plan on using this 'true' value in an 'if' function..
>
> > >> For example (the formula would be in column C):
>
> > >> Column A Column B Column C
> > >> Wile E. Coyote coyote true
> > >> Wile E. Coyote Wile true
>
> > >> I hope my example is clear. Can someone provide some guidance?
>
> > >> TIA
>
> > >I need to shift gears here but unfortunately I can't edit my post. *I
> > >want something like a 'vlookup' that matches any of the words in
> > >column A with column B and returns column B. *Something like this:
>
> > >=vlookup('any word in column A',column B,1,false)
>
> > It is not clear what you want to match. *Claus's response will returnTrue if any of the words in A1 are found in B1 and can be easily modified to return the word, or FALSE if the word is not there. *Something like:
>
> > =if(ISNUMBER(SEARCH(B1,A1)),B1)
>
> > If you want to know if ANY word in column B (all the cells) matches ANYword in column A (all the cells), you will have multiple matches. *How do you want to handle that?
>
> > Perhaps you only want to compare the phrase in A1 with a list of words in column B, returning either the word in column B, or FALSE? *If that isthe case, it makes more sense, but you still have to decide what you want to do in the event that multiple words in the list in column B are includedin the Phrase in A1.
>
> I just want to compare the phrase in one cell (A1 for example) to a
> range of cells in column B (for example B1:B10) and wherever the match
> is found return the text in the B cell. *With the list I am using
> there shouldn't be any duplicates.
>
> For example
>
> Cell A1 * * * * * * * * * * * * * * * Column B * * * * * * * * Cell C1
> (has formula)
> Wile E. Coyote * * * * * * * * * *apple * * * * * * * * * * * *coyote
> (the match was found in B5 and I want the formula to put the text in
> the match cell here)
> * * * * * * * * * * * * * * * * * * * * * blue
> * * * * * * * * * * * * * * * * * * * * * cherry
> * * * * * * * * * * * * * * * * * * * * * gun
> * * * * * * * * * * * * * * * * * * * * * coyote

Well my sentence wrapped around. I want cell C1 that contains the
formula to display the text in whatever cell in column B where the
match occurred.

On Apr 15, 7:31*pm, Ron Rosenfeld <r...@nospam.net> wrote:
> On Fri, 15 Apr 2011 16:55:21 -0700 (PDT), bird_...@my-deja.com wrote:
> >I just want to compare the phrase in one cell (A1 for example) to a
> >range of cells in column B (for example B1:B10) and wherever the match
> >is found return the text in the B cell. *With the list I am using
> >there shouldn't be any duplicates.
>
> That's much more clear.
>
> If you don't mind the possibility of words contained within other words; in other words:
>
> * * A * * * * * B * * * * * * * C
> blueberry * * * apple * * * * * * * * berry
> * * * * * * * * * * *berry
>
> Then you can use one of these:
>
> WordList is a contiguous array (no blanks) of your words in column B.
>
> These formulas must be **array-entered**:
>
> For Excel 2007 or later:
>
> =IFERROR(INDEX(WordList,MATCH(TRUE,ISNUMBER(SEARCH(WordList,A1)),0)),"")
>
> For earlier versions, which do not have the IFERROR function:
>
> =IF(ISNA(INDEX(WordList,MATCH(TRUE,
> ISNUMBER(SEARCH(WordList,A1)),0))),"",
> INDEX(WordList,MATCH(TRUE,
> ISNUMBER(SEARCH(WordList,A1)),0)))
>
> ----------------------------------------
>
> To **array-enter** a formula, after entering
> the formula into the cell or formula bar, hold down
> <ctrl><shift> while hitting <enter>. *If you did this
> correctly, Excel will place braces {...} around the formula.
>
> ---------------------------------------------------
>
> If you do not want "blue" to match "blueberry", then the easiest solutionwill be with a VBA User Defined Function. *If your words in the column Acells might not start/end with a letter/digit/underscore, the pattern in the UDF may need some modification.
>
> To enter this User Defined Function (UDF), <alt-F11> opens the Visual Basic Editor.
> Ensure your project is highlighted in the Project Explorer window.
> Then, from the top menu, select Insert/Module and
> paste the code below into the window that opens.
>
> To use this User Defined Function (UDF), enter a formula like
>
> =MatchWord(A1, WordList)
>
> *in some cell.
>
> ======================================
> Option Explicit
> Function MatchWord(Phrase As String, WordList As Range) As String
> * * Dim re As Object, mc As Object
> * * Dim sPat As String
> * * Dim c As Range
> Set re = CreateObject("vbscript.regexp")
>
> sPat = "\b("
> For Each c In WordList
> * * If Len(c.Text) > 0 Then sPat = sPat & c.Text & "|"
> Next c
> * * sPat = Left(sPat, Len(sPat) - 1) & ")\b"
>
> With re
> * * .Global = True
> * * .Pattern = sPat
> * * .ignorecase = True
> End With
>
> If re.test(Phrase) Then
> * * Set mc = re.Execute(Phrase)
> * * MatchWord = mc(0)
> End If
>
> End Function
> =============================

Thanks How can I modify this to give the text exactly 2 columns (or
whatever number of columns) over from the match? Like how 'vlookup'
works. Is it possible to modify this to deal with blank cells or will
it get way too complicated?

On Apr 15, 8:13*pm, bird_...@my-deja.com wrote:
> On Apr 15, 7:31*pm, Ron Rosenfeld <r...@nospam.net> wrote:
>
>
>
> > On Fri, 15 Apr 2011 16:55:21 -0700 (PDT), bird_...@my-deja.com wrote:
> > >I just want to compare the phrase in one cell (A1 for example) to a
> > >range of cells in column B (for example B1:B10) and wherever the match
> > >is found return the text in the B cell. *With the list I am using
> > >there shouldn't be any duplicates.
>
> > That's much more clear.
>
> > If you don't mind the possibility of words contained within other words; in other words:
>
> > * * A * * * * * B * * * * * * * C
> > blueberry * * * apple * * * * * * * * berry
> > * * * * * * * * * * *berry
>
> > Then you can use one of these:
>
> > WordList is a contiguous array (no blanks) of your words in column B.
>
> > These formulas must be **array-entered**:
>
> > For Excel 2007 or later:
>
> > =IFERROR(INDEX(WordList,MATCH(TRUE,ISNUMBER(SEARCH(WordList,A1)),0)),"")
>
> > For earlier versions, which do not have the IFERROR function:
>
> > =IF(ISNA(INDEX(WordList,MATCH(TRUE,
> > ISNUMBER(SEARCH(WordList,A1)),0))),"",
> > INDEX(WordList,MATCH(TRUE,
> > ISNUMBER(SEARCH(WordList,A1)),0)))
>
> > ----------------------------------------
>
> > To **array-enter** a formula, after entering
> > the formula into the cell or formula bar, hold down
> > <ctrl><shift> while hitting <enter>. *If you did this
> > correctly, Excel will place braces {...} around the formula.
>
> > ---------------------------------------------------
>
> > If you do not want "blue" to match "blueberry", then the easiest solution will be with a VBA User Defined Function. *If your words in the columnA cells might not start/end with a letter/digit/underscore, the pattern inthe UDF may need some modification.
>
> > To enter this User Defined Function (UDF), <alt-F11> opens the Visual Basic Editor.
> > Ensure your project is highlighted in the Project Explorer window.
> > Then, from the top menu, select Insert/Module and
> > paste the code below into the window that opens.
>
> > To use this User Defined Function (UDF), enter a formula like
>
> > =MatchWord(A1, WordList)
>
> > *in some cell.
>
> > ======================================
> > Option Explicit
> > Function MatchWord(Phrase As String, WordList As Range) As String
> > * * Dim re As Object, mc As Object
> > * * Dim sPat As String
> > * * Dim c As Range
> > Set re = CreateObject("vbscript.regexp")
>
> > sPat = "\b("
> > For Each c In WordList
> > * * If Len(c.Text) > 0 Then sPat = sPat & c.Text & "|"
> > Next c
> > * * sPat = Left(sPat, Len(sPat) - 1) & ")\b"
>
> > With re
> > * * .Global = True
> > * * .Pattern = sPat
> > * * .ignorecase = True
> > End With
>
> > If re.test(Phrase) Then
> > * * Set mc = re.Execute(Phrase)
> > * * MatchWord = mc(0)
> > End If
>
> > End Function
> > =============================
>
> Thanks *How can I modify this to give the text exactly 2 columns (or
> whatever number of columns) over from the match? *Like how 'vlookup'
> works. *Is it possible to modify this to deal with blank cells or will
> it get way too complicated?

This formula
{=IF(ISNA(INDEX(c2:c6,MATCH(TRUE,ISNUMBER(SEARCH(c2:c6,A1)),
0))),"",INDEX(c2:c6,MATCH(TRUE,ISNUMBER(SEARCH(c2:c6,A1)),0)))}
doesn't seem to work in Excel 2010. I am testing this on Excel 2010,
but it will be used on an older version of Excel.

On Apr 15, 8:37*pm, Ron Rosenfeld <r...@nospam.net> wrote:
> On Fri, 15 Apr 2011 18:13:43 -0700 (PDT), bird_...@my-deja.com wrote:
> >Thanks *How can I modify this to give the text exactly 2 columns (or
> >whatever number of columns) over from the match? *Like how 'vlookup'
> >works. *Is it possible to modify this to deal with blank cells or will
> >it get way too complicated?
>
> Your column of words "WordList" is where ever you put it. *If your phrases are in column A, and your list of words is in Column C, just use C1:Cn as the argument.

This is what I mean. The phrase is in cell A1, the match is in cell
B6, the text I want to put in cell C1 (where the formula is) is in
cell D6 (two columns over from the match).

On Apr 15, 8:44*pm, Ron Rosenfeld <r...@nospam.net> wrote:
> On Fri, 15 Apr 2011 18:24:59 -0700 (PDT), bird_...@my-deja.com wrote:
> >This formula
> >{=IF(ISNA(INDEX(c2:c6,MATCH(TRUE,ISNUMBER(SEARCH(c2:c6,A1)),
> >0))),"",INDEX(c2:c6,MATCH(TRUE,ISNUMBER(SEARCH(c2:c6,A1)),0)))}
> >doesn't seem to work in Excel 2010. *I am testing this on Excel 2010,
> >but it will be used on an older version of Excel.
>
> What do you mean by "doesn't seem to work"? *That phrase is not very helpful as there are many ways a formula might fail. *Describing the failure can be helpful in determining what the problem might be.
>
> I can think of no reason why that formula should not work in any version of Excel. *My guess is that you are either entering it incorrectly; or the environment is not exactly how you described it. *But unless you supplymore information, it is difficult to troubleshoot.
>
> I notice that you have curly brackets on either side of your formula in your message. *How did they get into your message here?

I just put them there to show that I entered it as an array. I guess
I shouldn't have posted the formula like that. Well it seems to be
working now. Excel was giving me the standard 'there's a problem with
your formula' error before.

On Apr 16, 9:29*am, Ron Rosenfeld <r...@nospam.net> wrote:
> On Sat, 16 Apr 2011 04:14:24 -0700 (PDT), bird_...@my-deja.com wrote:
> >On Apr 15, 8:37*pm, Ron Rosenfeld <r...@nospam.net> wrote:
> >> On Fri, 15 Apr 2011 18:13:43 -0700 (PDT), bird_...@my-deja.com wrote:
> >> >Thanks *How can I modify this to give the text exactly 2 columns (or
> >> >whatever number of columns) over from the match? *Like how 'vlookup'
> >> >works. *Is it possible to modify this to deal with blank cells or will
> >> >it get way too complicated?
>
> >> Your column of words "WordList" is where ever you put it. *If your phrases are in column A, and your list of words is in Column C, just use C1:Cn as the argument.
>
> >This is what I mean. *The phrase is in cell A1, *the match is in cell
> >B6, the text I want to put in cell C1 (where the formula is) is in
> >cell D6 (two columns over from the match).
>
> If WordList is the list of words in column B as before; and MatchList is your "text to return" in column D (and it must have the same number of rowsas does WordList) then this **array-entered** formula should do the trick:
>
> =IF(ISNA(MATCH(TRUE,ISNUMBER(SEARCH(
> WordList,A2)),0)),"",INDEX(MatchList,MATCH(
> TRUE,ISNUMBER(SEARCH(WordList,A2)),0)))
>
> Note that we merely change the "array" argument in the INDEX function to point to the array from which you want to return the answer.
>
> This, obviously, has the same limitation as before with regard to "whole words", but I guess that is not a problem for you.

Thanks for all your help! How can this be modified to deal with blank
cells in the 'phrase list' column?

On Apr 16, 1:01*pm, Ron Rosenfeld <r...@nospam.net> wrote:
> On Sat, 16 Apr 2011 09:53:27 -0700 (PDT), bird_...@my-deja.com wrote:
> >Thanks for all your help! *How can this be modified to deal with blank
> >cells in the 'phrase list' column?
>
> What happened when you tried it?
>
> If you entered it correctly, the formula should be returning a null string, which appears blank, if the cell in the Phrase List column is empty.

It works fine on the test data. Could you explain what this formula
is doing?