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!