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)

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

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?

On Apr 15, 8:13*pm, wrote:
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?

This formula
{=IF(ISNA(INDEX(c2:c6,MATCH(TRUE,ISNUMBER(SEARCH(c 2: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 Fri, 15 Apr 2011 18:13:43 -0700 (PDT), 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.

On Fri, 15 Apr 2011 18:24:59 -0700 (PDT), 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 supply more 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?

On Apr 15, 8:37*pm, Ron Rosenfeld wrote:
On Fri, 15 Apr 2011 18:13:43 -0700 (PDT), 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 wrote:
On Fri, 15 Apr 2011 18:24:59 -0700 (PDT), 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 supply more 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 Sat, 16 Apr 2011 04:25:50 -0700 (PDT), wrote:

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.

Since you didn't copy/paste your formula originally, it is hard to tell what was wrong; but most likely there was some type when you entered it initially.

On Sat, 16 Apr 2011 04:14:24 -0700 (PDT), wrote:

On Apr 15, 8:37*pm, Ron Rosenfeld wrote:
On Fri, 15 Apr 2011 18:13:43 -0700 (PDT), 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 rows as 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.

On Apr 16, 9:29*am, Ron Rosenfeld wrote:
On Sat, 16 Apr 2011 04:14:24 -0700 (PDT), wrote:
On Apr 15, 8:37*pm, Ron Rosenfeld wrote:
On Fri, 15 Apr 2011 18:13:43 -0700 (PDT), 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 rows as 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 Sat, 16 Apr 2011 09:53:27 -0700 (PDT), 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.

On Apr 16, 1:01*pm, Ron Rosenfeld wrote:
On Sat, 16 Apr 2011 09:53:27 -0700 (PDT), 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?

On Sat, 16 Apr 2011 12:02:38 -0700 (PDT), wrote:

On Apr 16, 1:01*pm, Ron Rosenfeld wrote:
On Sat, 16 Apr 2011 09:53:27 -0700 (PDT), 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?

Refer also to the Evaluate Formula wizard, and the HELP files

=IF(ISNA(MATCH(TRUE,ISNUMBER(SEARCH(
WordList,A2)),0)),"",INDEX(MatchList,MATCH(
TRUE,ISNUMBER(SEARCH(WordList,A2)),0)))


SEARCH(WordList,A2)

Searches each entry in WordList to see if it can be found in Cell A2. If it is found, it will return a number indicating the position. If it is not found, it will return a #VALUE! error . So if entry number three in WordList was found in A2; this function would return the array: {#VALUE!;#VALUE!;4;#VALUE!;VALUE!} The 4 represents the position of the 4th word in WordList in the Phrase in A2.

Nest that result in the ISNUMBER function:

ISNUMBER( {#VALUE!;#VALUE!;4;#VALUE!;VALUE!} ): Returns {FALSE;FALSE;TRUE;FALSE;FALSE}

The MATCH function, will then tell us where TRUE (actually, the first TRUE if there is more than one) is located:

MATCH(TRUE,{FALSE;FALSE;TRUE;FALSE;FALSE},0) -- 3

3 is then used in the INDEX function to return the appropriate entry from MatchList.

Using the Evaluate Formula dialog, you should be able to figure out the rest. Post back if there is something specific you do not understand.

On Apr 16, 2:51*pm, Ron Rosenfeld wrote:
On Sat, 16 Apr 2011 12:02:38 -0700 (PDT), wrote:
On Apr 16, 1:01*pm, Ron Rosenfeld wrote:
On Sat, 16 Apr 2011 09:53:27 -0700 (PDT), 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?

Refer also to the Evaluate Formula wizard, and the HELP files

=IF(ISNA(MATCH(TRUE,ISNUMBER(SEARCH(
WordList,A2)),0)),"",INDEX(MatchList,MATCH(
TRUE,ISNUMBER(SEARCH(WordList,A2)),0)))

SEARCH(WordList,A2)

Searches each entry in WordList to see if it can be found in Cell A2. *If it is found, it will return a number indicating the position. *If it is not found, it will return a #VALUE! error . *So if entry number three in WordList was found in A2; this function would return the array: *{#VALUE!;#VALUE!;4;#VALUE!;VALUE!} * The 4 represents the position of the 4th word in WordList in the Phrase in A2.

Nest that result in the ISNUMBER function:

ISNUMBER( {#VALUE!;#VALUE!;4;#VALUE!;VALUE!} ): *Returns {FALSE;FALSE;TRUE;FALSE;FALSE}

The MATCH function, will then tell us where TRUE (actually, the first TRUE if there is more than one) is located:

MATCH(TRUE,{FALSE;FALSE;TRUE;FALSE;FALSE},0) -- 3

3 is then used in the INDEX function to return the appropriate entry from MatchList.

Using the Evaluate Formula dialog, you should be able to figure out the rest. *Post back if there is something specific you do not understand.

How can this be modified to deal with blank cells in the 'wordlist'
column?

Thanks!

On Mon, 18 Apr 2011 06:43:51 -0700 (PDT), wrote:

How can this be modified to deal with blank cells in the 'wordlist'
column?

Thanks!

I don't know how to modify the formula to do that. Perhaps some lurker can figure it out, if it is possible.

I would either use a VBA User Defined Function, or, if that is not allowed by your company, I would ensure that there are no blank cells in WordList.

The VBA UDF would be based on what I presented in my second response to you a few days ago. If that is what you want, you can either modify it yourself or, if you will present a full list of specifications in a single message, (as opposed to one new specification per message, which is what you have been doing), I would be happy to make the appropriate modifications.

presented the following explanation :
On Apr 16, 2:51*pm, Ron Rosenfeld wrote:
On Sat, 16 Apr 2011 12:02:38 -0700 (PDT), wrote:
On Apr 16, 1:01*pm, Ron Rosenfeld wrote:
On Sat, 16 Apr 2011 09:53:27 -0700 (PDT), 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?

Refer also to the Evaluate Formula wizard, and the HELP files

=IF(ISNA(MATCH(TRUE,ISNUMBER(SEARCH(
WordList,A2)),0)),"",INDEX(MatchList,MATCH(
TRUE,ISNUMBER(SEARCH(WordList,A2)),0)))

SEARCH(WordList,A2)

Searches each entry in WordList to see if it can be found in Cell A2. *If it
is found, it will return a number indicating the position. *If it is not
found, it will return a #VALUE! error . *So if entry number three in
WordList was found in A2; this function would return the array:
*{#VALUE!;#VALUE!;4;#VALUE!;VALUE!} * The 4 represents the position of the
4th word in WordList in the Phrase in A2.

Nest that result in the ISNUMBER function:

ISNUMBER( {#VALUE!;#VALUE!;4;#VALUE!;VALUE!} ): *Returns
{FALSE;FALSE;TRUE;FALSE;FALSE}

The MATCH function, will then tell us where TRUE (actually, the first TRUE
if there is more than one) is located:

MATCH(TRUE,{FALSE;FALSE;TRUE;FALSE;FALSE},0) -- 3

3 is then used in the INDEX function to return the appropriate entry from
MatchList.

Using the Evaluate Formula dialog, you should be able to figure out the
rest. *Post back if there is something specific you do not understand.

How can this be modified to deal with blank cells in the 'wordlist'
column?

Thanks!

Since it's not normal to have blanks in a 'list', why are blank cells
there?

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

On Apr 18, 1:53*pm, GS wrote:
presented the following explanation :



On Apr 16, 2:51*pm, Ron Rosenfeld wrote:
On Sat, 16 Apr 2011 12:02:38 -0700 (PDT), wrote:
On Apr 16, 1:01*pm, Ron Rosenfeld wrote:
On Sat, 16 Apr 2011 09:53:27 -0700 (PDT), 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?

Refer also to the Evaluate Formula wizard, and the HELP files

=IF(ISNA(MATCH(TRUE,ISNUMBER(SEARCH(
WordList,A2)),0)),"",INDEX(MatchList,MATCH(
TRUE,ISNUMBER(SEARCH(WordList,A2)),0)))

SEARCH(WordList,A2)

Searches each entry in WordList to see if it can be found in Cell A2. *If it
is found, it will return a number indicating the position. *If it is not
found, it will return a #VALUE! error . *So if entry number three in
WordList was found in A2; this function would return the array:
*{#VALUE!;#VALUE!;4;#VALUE!;VALUE!} * The 4 represents the position of the
4th word in WordList in the Phrase in A2.

Nest that result in the ISNUMBER function:

ISNUMBER( {#VALUE!;#VALUE!;4;#VALUE!;VALUE!} ): *Returns
{FALSE;FALSE;TRUE;FALSE;FALSE}

The MATCH function, will then tell us where TRUE (actually, the first TRUE
if there is more than one) is located:

MATCH(TRUE,{FALSE;FALSE;TRUE;FALSE;FALSE},0) -- 3

3 is then used in the INDEX function to return the appropriate entry from
MatchList.

Using the Evaluate Formula dialog, you should be able to figure out the
rest. *Post back if there is something specific you do not understand.

How can this be modified to deal with blank cells in the 'wordlist'
column?

Thanks!

Since it's not normal to have blanks in a 'list', why are blank cells
there?

--
Garry

Free usenet access athttp://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

It's not really a 'list' per se, it's just a column of text values and
there are a couple of blank spots in it.

On Apr 18, 1:41*pm, Ron Rosenfeld wrote:
On Mon, 18 Apr 2011 06:43:51 -0700 (PDT), wrote:
How can this be modified to deal with blank cells in the 'wordlist'
column?

Thanks!

I don't know how to modify the formula to do that. *Perhaps some lurker can figure it out, if it is possible.

I would either use a VBA User Defined Function, or, if that is not allowed by your company, I would ensure that there are no blank cells in WordList.. *

The VBA UDF would be based on what I presented in my second response to you a few days ago. *If that is what you want, you can either modify it yourself or, if you will present a full list of specifications in a single message, (as opposed to one new specification per message, which is what you have been doing), I would be happy to make the appropriate modifications.

Not knowing what I was doing I made this modification based on
searching around the net. =IF(ISNA(MATCH(TRUE,ISNUMBER(SEARCH($C$2:$C
$26,A2)),0)),"",INDEX($E$2:$E$26,MATCH(TRUE,ISNUMB ER(SEARCH($C$2:$C
$26,A2)-COUNTIF($C$2:$C$26,0))),0)

This just gives me the last text in the list instead of not counting
the blank cells. I may look into modifying that UDF posted earlier
however, I don't think it was written to return the text that was x
number of columns over from the match so I don't think I'll be
successful at modifying it. :)

On Mon, 18 Apr 2011 16:14:16 -0700 (PDT), wrote:


This just gives me the last text in the list instead of not counting
the blank cells. I may look into modifying that UDF posted earlier
however, I don't think it was written to return the text that was x
number of columns over from the match so I don't think I'll be
successful at modifying it. :)

If all you need is to return something that is a few columns over, you can use the Index(Match... method within the UDF, just as you would outside the UDF. You need to add a third argument for the MatchList, but that modification is easy:

=========================
Option Explicit
Function MatchWord(Phrase As String, WordList As Range, MatchList As Range) As String
Dim re As Object, mc As Object
Dim sPat As String
Dim c As Range
If WordList.Rows.Count MatchList.Rows.Count Then
MsgBox ("WordList cannot be longer than Matchlist")
Exit Function
End If
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)
With WorksheetFunction
MatchWord = .Index(MatchList, .Match(mc(0), WordList, 0))
End With
End If

End Function
===================================

And then your formula might be:

=MatchWord(A2,$B$1:$B$12,$D$1:$D$12)

Because of using the Index(Match... construct, WordList cannot be longer than MatchList, or else there will be an error, so we check for that and exit the function with a message if that situation should be present.

On Mon, 18 Apr 2011 19:40:50 -0400, Ron Rosenfeld wrote:

On Mon, 18 Apr 2011 16:14:16 -0700 (PDT), wrote:


This just gives me the last text in the list instead of not counting
the blank cells. I may look into modifying that UDF posted earlier
however, I don't think it was written to return the text that was x
number of columns over from the match so I don't think I'll be
successful at modifying it. :)

If all you need is to return something that is a few columns over, you can use the Index(Match... method within the UDF, just as you would outside the UDF. You need to add a third argument for the MatchList, but that modification is easy:

=========================
Option Explicit
Function MatchWord(Phrase As String, WordList As Range, MatchList As Range) As String
Dim re As Object, mc As Object
Dim sPat As String
Dim c As Range
If WordList.Rows.Count MatchList.Rows.Count Then
MsgBox ("WordList cannot be longer than Matchlist")
Exit Function
End If
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)
With WorksheetFunction
MatchWord = .Index(MatchList, .Match(mc(0), WordList, 0))
End With
End If

End Function
===================================

And then your formula might be:

=MatchWord(A2,$B$1:$B$12,$D$1:$D$12)

Because of using the Index(Match... construct, WordList cannot be longer than MatchList, or else there will be an error, so we check for that and exit the function with a message if that situation should be present.



An alternative, if you didn't want to worry about MatchList vs WordList size, would be to use Match to find the location of the word in column B; and then use Offset to return the result a few columns over; but that method is not as flexible.

On Apr 18, 6:44*pm, Ron Rosenfeld wrote:
On Mon, 18 Apr 2011 19:40:50 -0400, Ron Rosenfeld wrote:
On Mon, 18 Apr 2011 16:14:16 -0700 (PDT), wrote:

This just gives me the last text in the list instead of not counting
the blank cells. *I may look into modifying that UDF posted earlier
however, I don't think it was written to return the text that was x
number of columns over from the match so I don't think I'll be
successful at modifying it. :)

If all you need is to return something that is a few columns over, you can use the Index(Match... method within the UDF, just as you would outside the UDF. *You need to add a third argument for the MatchList, but that modification is easy:

=========================
Option Explicit
Function MatchWord(Phrase As String, WordList As Range, MatchList As Range) As String
* *Dim re As Object, mc As Object
* *Dim sPat As String
* *Dim c As Range
If WordList.Rows.Count MatchList.Rows.Count Then
* *MsgBox ("WordList cannot be longer than Matchlist")
* *Exit Function
End If
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)
* *With WorksheetFunction
* * * *MatchWord = .Index(MatchList, .Match(mc(0), WordList, 0))
* *End With
End If

End Function
===================================

And then your formula might be:

=MatchWord(A2,$B$1:$B$12,$D$1:$D$12)

Because of using the Index(Match... construct, WordList cannot be longer than MatchList, or else there will be an error, so we check for that and exit the function with a message if that situation should be present.

An alternative, if you didn't want to worry about MatchList vs WordList size, would be to use Match to find the location of the word in column B; and then use Offset to return the result a few columns over; but that method is not as flexible.

Ok I am just now getting back to this and I am having a problem on
actual data.

This is the formula I am using. Column C is the 'WordList' Column E
is the 'MatchList' and cell A2 (actually any part of A2) is what I
want to find in column C.
=IF(ISNA(MATCH(TRUE,ISNUMBER(SEARCH($C$2:$C$6,A2)) ,0)),"",INDEX($E$2:$E
$6,MATCH(TRUE,ISNUMBER(SEARCH($C$2:$C$6,A2)),0)))

This is what is in 'A2' "700 W FOREST AVE" (no quotes). This is in
Column C "700 West Forest Suite 200". I would think that it would
match either on '700' or 'Forest', but it doesn't and just returns a
blank because it gives an 'NA#' error.

Please advise

On Tue, 3 May 2011 13:30:04 -0700 (PDT), wrote:

Ok I am just now getting back to this and I am having a problem on
actual data.

This is the formula I am using. Column C is the 'WordList' Column E
is the 'MatchList' and cell A2 (actually any part of A2) is what I
want to find in column C.
=IF(ISNA(MATCH(TRUE,ISNUMBER(SEARCH($C$2:$C$6,A2) ),0)),"",INDEX($E$2:$E
$6,MATCH(TRUE,ISNUMBER(SEARCH($C$2:$C$6,A2)),0) ))

This is what is in 'A2' "700 W FOREST AVE" (no quotes). This is in
Column C "700 West Forest Suite 200". I would think that it would
match either on '700' or 'Forest', but it doesn't and just returns a
blank because it gives an 'NA#' error.

Please advise

Most likely, you did not enter this as an array formula.

Your formula must be **array-entered**:

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.

On May 5, 5:34*pm, Ron Rosenfeld wrote:
On Tue, 3 May 2011 13:30:04 -0700 (PDT), wrote:
Ok I am just now getting back to this and I am having a problem on
actual data.

This is the formula I am using. *Column C is the 'WordList' *Column E
is the 'MatchList' and cell A2 (actually any part of A2) is what I
want to find in column C.
=IF(ISNA(MATCH(TRUE,ISNUMBER(SEARCH($C$2:$C$6,A2) ),0)),"",INDEX($E$2:$E
$6,MATCH(TRUE,ISNUMBER(SEARCH($C$2:$C$6,A2)),0) ))

This is what is in 'A2' *"700 W FOREST AVE" (no quotes). * This is in
Column C *"700 West Forest Suite 200". *I would think that it would
match either on '700' or 'Forest', but it doesn't and just returns a
blank because it gives an 'NA#' error.

Please advise

Most likely, you did not enter this as an array formula.

Your formula must be **array-entered**:

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.

No it is entered as an array.

On Mon, 9 May 2011 10:43:17 -0700 (PDT), wrote:

On May 5, 5:34*pm, Ron Rosenfeld wrote:
On Tue, 3 May 2011 13:30:04 -0700 (PDT), wrote:
Ok I am just now getting back to this and I am having a problem on
actual data.

This is the formula I am using. *Column C is the 'WordList' *Column E
is the 'MatchList' and cell A2 (actually any part of A2) is what I
want to find in column C.
=IF(ISNA(MATCH(TRUE,ISNUMBER(SEARCH($C$2:$C$6,A2) ),0)),"",INDEX($E$2:$E
$6,MATCH(TRUE,ISNUMBER(SEARCH($C$2:$C$6,A2)),0) ))

This is what is in 'A2' *"700 W FOREST AVE" (no quotes). * This is in
Column C *"700 West Forest Suite 200". *I would think that it would
match either on '700' or 'Forest', but it doesn't and just returns a
blank because it gives an 'NA#' error.

Please advise

Most likely, you did not enter this as an array formula.

Your formula must be **array-entered**:

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.

No it is entered as an array.

Then there is something that I have misunderstood about your data. If you could post a sample of your workbook on one of the file sharing sites, and post a link, I can download it and look at it to see where the problem lies.

On May 9, 3:32*pm, Ron Rosenfeld wrote:
On Mon, 9 May 2011 10:43:17 -0700 (PDT), wrote:
On May 5, 5:34*pm, Ron Rosenfeld wrote:
On Tue, 3 May 2011 13:30:04 -0700 (PDT), wrote:
Ok I am just now getting back to this and I am having a problem on
actual data.

This is the formula I am using. *Column C is the 'WordList' *Column E
is the 'MatchList' and cell A2 (actually any part of A2) is what I
want to find in column C.
=IF(ISNA(MATCH(TRUE,ISNUMBER(SEARCH($C$2:$C$6,A2) ),0)),"",INDEX($E$2:$E
$6,MATCH(TRUE,ISNUMBER(SEARCH($C$2:$C$6,A2)),0) ))

This is what is in 'A2' *"700 W FOREST AVE" (no quotes). * This is in
Column C *"700 West Forest Suite 200". *I would think that it would
match either on '700' or 'Forest', but it doesn't and just returns a
blank because it gives an 'NA#' error.

Please advise

Most likely, you did not enter this as an array formula.

Your formula must be **array-entered**:

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.

No it is entered as an array.

Then there is something that I have misunderstood about your data. *If you could post a sample of your workbook on one of the file sharing sites, and post a link, I can download it and look at it to see where the problem lies.

Ok here is the link for the file.
http://uploading.com/files/5274ec51/Formula%2Btest.xls/

See the 'Info' tab for what I am trying to accomplish. Thanks

On Tue, 17 May 2011 07:33:30 -0700 (PDT), wrote:

Ok here is the link for the file.
http://uploading.com/files/5274ec51/Formula%2Btest.xls/

See the 'Info' tab for what I am trying to accomplish. Thanks

I've had a chance to look at your spreadsheet.

Your data isn't matching what you think it is, therefore you are not going to return anything.

You want to use data in column I to match items in C2:C26 and return what is in the same row in Col E of the two spreadsheets.

But consider:

I161: 42 AUTUMNWOOD CV

You expect to see, as noted in N161: MCMP

But in C2:C26, there is no Autumnwood.

With regard to

700 W FOREST AVE

There is no way it can match with

700 West Forest Suite 200


One is not included in the other.

Also:

I99: 1700 WOODLAWN AVE
N99: MedSouth

But the only thing similar in C2:C26 is:

17X0 Woodlawn

Again, this will not match with your current rules.

I note that many of your desired MCMP matches seem to be based on Column F data and not Column I data. Obviously, none of them will work. That is also true for others of your desired matches: sometimes referring to COL I but other times to COL F

---------------------------------------------

The formula works for the rule that you stated which had to do with matching text in one column with the text in another column and returning information a few columns over. But your rule does not describe what you want to do.

You are going to need much more specific rules that what you have supplied in order to develop a matching system that will be non-ambiguous.

As of now, sometimes you want to match the DestinationAddress1; sometimes the ShipperAddress1, but how do you decide?

Also, it seems as if you want to use some kind of fuzzy matching, and that is beyond what I can do for you here. You are going to have to "clean up" the data, or come up with some very specific rules for matching fragments.

On May 20, 7:36*pm, Ron Rosenfeld wrote:
On Tue, 17 May 2011 07:33:30 -0700 (PDT), wrote:
Ok here is the link for the file.
http://uploading.com/files/5274ec51/Formula%2Btest.xls/

See the 'Info' tab for what I am trying to accomplish. *Thanks

I've had a chance to look at your spreadsheet.

Your data isn't matching what you think it is, therefore you are not going to return anything.

You want to use data in column I to match items in C2:C26 and return what is in the same row in Col E of the two spreadsheets.

But consider:

I161: * 42 AUTUMNWOOD CV

You expect to see, as noted in N161: * *MCMP

But in C2:C26, there is no Autumnwood.

With regard to

700 W FOREST AVE

There is no way it can match with

700 West Forest Suite 200

One is not included in the other.

Also:

I99: * *1700 WOODLAWN AVE
N99: * *MedSouth

But the only thing similar in C2:C26 is:

17X0 Woodlawn

Again, this will not match with your current rules.

I note that many of your desired MCMP matches seem to be based on Column F data and not Column I data. *Obviously, none of them will work. *That is also true for others of your desired matches: sometimes referring to COL I but other times to COL F

---------------------------------------------

The formula works for the rule that you stated which had to do with matching text in one column with the text in another column and returning information a few columns over. *But your rule does not describe what you want to do.

You are going to need much more specific rules that what you have supplied in order to develop a matching system that will be non-ambiguous.

As of now, sometimes you want to match the DestinationAddress1; sometimes the ShipperAddress1, but how do you decide?

Also, it seems as if you want to use some kind of fuzzy matching, and that is beyond what I can do for you here. *You are going to have to "clean up" the data, or come up with some very specific rules for matching fragments.

Thanks for looking at this. Yes, I will need to look at multiple
columns to match but I was figuring if I could get the basic 'match'
formula I could adapt it to handle the additional columns. Can you
explain for example why the the '700 W Forest Av' doesn't match? I.e.
why doesn't it match on the word 'Forest'?

Thanks!

On Mon, 23 May 2011 06:10:33 -0700 (PDT), wrote:

Thanks for looking at this. Yes, I will need to look at multiple
columns to match but I was figuring if I could get the basic 'match'
formula I could adapt it to handle the additional columns. Can you
explain for example why the the '700 W Forest Av' doesn't match? I.e.
why doesn't it match on the word 'Forest'?

700 W FOREST AVE

700 West Forest Suite 200


The match and various comparison functions look for one string within another. They don't, for example, look to see that there are matches of the:
First Word: 700
First letter of second word: W
Third word: Forest
no match of the fourth word: AVE

and conclude, "Well, that's good enough; let's call it a match!"

It doesn't match because you have not stated any rules for matching fragments. So looking for "700 W FOREST AVE" within the string "700 West Forest Suite 200" will fail after the "W"

A human can tell that this is likely at the same street address, but there are certainly any number of towns where there might be, for example, a Forest Ave; Forest St.; Forest Blvd; etc.

On May 23, 8:41*pm, Ron Rosenfeld wrote:
On Mon, 23 May 2011 06:10:33 -0700 (PDT), wrote:
Thanks for looking at this. *Yes, I will need to look at multiple
columns to match but I was figuring if I could get the basic 'match'
formula I could adapt it to handle the additional columns. *Can you
explain for example why the the '700 W Forest Av' doesn't match? *I.e.
why doesn't it match on the word 'Forest'?
700 W FOREST AVE

700 West Forest Suite 200

The match and various comparison functions look for one string within another. *They don't, for example, look to see that there are matches of the:
* * * * First Word: * * * * * * * * * * 700
* * * * First letter of second word: * *W
* * * * Third word: * * * * * * * * * * Forest
* * * * no match of the fourth word: * *AVE

and conclude, "Well, that's good enough; let's call it a match!"

It doesn't match because you have not stated any rules for matching fragments. *So looking for "700 W FOREST AVE" within the string "700 West Forest Suite 200" will fail after the "W"

A human can tell that this is likely at the same street address, but there are certainly any number of towns where there might be, for example, a Forest Ave; Forest St.; Forest Blvd; etc.

So I guess there is no easy way of doing this then. Thanks for your
input.