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, 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 Fri, 15 Apr 2011 09:45:08 -0700 (PDT), wrote:

On Apr 15, 11:19*am, 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 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 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 in column 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 to do in the event that multiple words in the list in column B are included in the Phrase in A1.

On Apr 15, 3:53*pm, Ron Rosenfeld wrote:
On Fri, 15 Apr 2011 09:45:08 -0700 (PDT), wrote:
On Apr 15, 11:19 am, 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 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 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 in column 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 to do 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, wrote:
On Apr 15, 3:53*pm, Ron Rosenfeld wrote:



On Fri, 15 Apr 2011 09:45:08 -0700 (PDT), wrote:
On Apr 15, 11:19 am, 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 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 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 in column 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 to do 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

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 Fri, 15 Apr 2011 16:55:21 -0700 (PDT), 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 column A cells 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
=============================

On Apr 15, 7:31*pm, Ron Rosenfeld wrote:
On Fri, 15 Apr 2011 16:55:21 -0700 (PDT), 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 column A cells 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?

Am Fri, 15 Apr 2011 09:19:11 -0700 (PDT) schrieb :

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

in C1: =ISNUMBER(SEARCH(B1,A1))


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2