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