# Thread: finding certain words in a cell and recording find (xp & 2003)

1. ## finding certain words in a cell and recording find (xp & 2003)

I am attaching a sample worksheet. I would like to find a certain word or phrase in a cell and record one of three different words. In column E is the "Description" which contain different phrases. Column E is where I would like the formula to record one of 4 words 1. REVERSAL, 2.ACCRUAL 3. MONTHLY ENTRY & 4. OTHER depending on what the criteria in the Description Cell is written.

2. ## Re: finding certain words in a cell and recording find (xp & 2003)

Don't you mean you want column F to be where you record your formula result?

3. ## Re: finding certain words in a cell and recording find (xp & 2003)

Try this in F2, and fill down:

=INDEX({"Reversal","Monthly Entry","Accrual","Monthly Entry"},MATCH(FALSE,ISERROR(SEARCH({"reversal","pa yroll j/e","accrual","adp payroll"},E2)),0))

4. ## Re: finding certain words in a cell and recording find (xp & 2003)

Hans,
Very interesting! Would you mind explaining how you solution works?
Thanks

5. ## Re: finding certain words in a cell and recording find (xp & 2003)

{"reversal","payroll j/e","accrual","adp payroll"} is an array containing the four search terms.

SEARCH({"reversal","payroll j/e","accrual","adp payroll"},E2) tries to find each of the search terms in cell E2 (not case-sensitive) and returns a a 4 element array: a number if found, an error if not found.

ISERROR(SEARCH({"reversal","payroll j/e","accrual","adp payroll"},E2)) returns a 4 element array: FALSE if found (SEARCH did *not* return an error), TRUE if not found.

MATCH(FALSE,ISERROR(SEARCH({"reversal","payroll j/e","accrual","adp payroll"},E2)),0) returns the position of the first FALSE in the array (or #N/A if all values are TRUE).

=INDEX({"Reversal","Monthly Entry","Accrual","Monthly Entry"},MATCH(FALSE,ISERROR(SEARCH({"reversal","pa yroll j/e","accrual","adp payroll"},E2)),0)) looks up the corresponding item in the array of return strings {"Reversal","Monthly Entry","Accrual","Monthly Entry"}.

Thanks Hans

7. ## Re: finding certain words in a cell and recording

Or try……..

F2, enter the formula and copy down :