search for multiple keywords in a cell and return positive resuly when ALL are present

[dori2o]

I'm creating a quiz sheet in excel where the user inputs their answer into a cell. (i.e Cell F5)

The idea is that if they enter their answer into the answer cell the cell in the next column of that row will show a 'Tick' (uppercase P in windings font) if the answer is corect. At all other times the cell shows a 'cross' (uppercase O in windings).

Once all questions have been answered and all are correct they can submit the worksheet to an email address for the sheets to be verified and a winner chosen at random.

In order to get the tick in the cell the answer must contain certain keywords that are essential to the answer. i.e. the answer cannot be correct without these keywords.

I'm using =IF(SUMPRODUCT(--ISNUMBER(SEARCH(Y18:Y20,F5))),"P","O") to check for keywords in cell F5.

However, currently the formula returns a positive result even if there is only 1 word from the keywords list (held in range Y18:Y20) within the answer.

How would I update the formula so that all of the words in the list have to present within the text string before a positive result is returned.

Also can anyone confirm that having blank cells in the keywords list will have no effect on the answer, and if so how would I rectify this?

Many Thanks in advance.

 

[admiral100]

Very nice Aladin !

 

[Rick Rothstein]

Very nice Aladin !

I think that might depend on what the OP meant by this line from his original post... "In order to get the tick in the cell the answer must contain certain keywords that are essential to the answer". Are the test takers writing sentences or just listing words (sounds like sentences to me). If sentences, the formula will fail if a keyword appears at the end of a sentence because of the period adjacent to it. If a list, the test taker must resist their instinct for using commas or other list separator characters to separate the listed words from each other. I think we may need the OP to come back to this thread and clarify how his quiz is supposed to work.

 

[dori2o]

[SOLVED] Re: search for multiple keywords in a cell and return positive resuly when ALL are present

I think that might depend on what the OP meant by this line from his original post... "In order to get the tick in the cell the answer must contain certain keywords that are essential to the answer". Are the test takers writing sentences or just listing words (sounds like sentences to me). If sentences, the formula will fail if a keyword appears at the end of a sentence because of the period adjacent to it. If a list, the test taker must resist their instinct for using commas or other list separator characters to separate the listed words from each other. I think we may need the OP to come back to this thread and clarify how his quiz is supposed to work.

Hi,

Thanks to everyone for their input.

This has now been solved using the User Defined Function.

To answer your question in the last comment.

The answer boxes will either contain sentences OR a single URL.

The UDF provides me the opportunity to apply all necessary keywords and URL's int he keyword range.

Thank you all very much.

 

[Aladin Akyurek]

Re: [SOLVED] Re: search for multiple keywords in a cell and return positive resuly when ALL are present

Hi,

Thanks to everyone for their input.

This has now been solved using the User Defined Function.

To answer your question in the last comment.

The answer boxes will either contain sentences OR a single URL.

The UDF provides me the opportunity to apply all necessary keywords and URL's int he keyword range.

Thank you all very much.

Post #10 does all that too, unless space as separator does not hold.

 

[Rick Rothstein]

Re: [SOLVED] Re: search for multiple keywords in a cell and return positive resuly when ALL are present

The answer boxes will either contain sentences OR a single URL.

Post #10 does all that too, unless space as separator does not hold.

What about the keyword next to the punctuation (period, question mark, exclamation mark) at the end of a sentence problem? And if it is a sentence, one might expect internal commas, hyphens and the such which, if next to a keyword, would also be a problem.