Complex Match Formula

[LadyDi]

I have a report that generates a column of data containing text entered by a service technician regarding the piece of equipment he is working on. I also have a column, labeled the "Bucket" column where I have five key words. I would like to be able to use a formula that will look in the call text and tell me which of my five "buckets" the call fits in. In other words, if the call text reads "printer jammed due to mis-feeding", I would want my formula to return the key word "Printer" (which would be listed in my bucket column). If the call text would read something like "screen locked due to power failure", I would want the formula to return a null value, because nothing in this text is one of my five key words. Is this possible?

 

[NBVC]

Assuming your comments start in A2, and that your bucket of 5 key words are in G2:G6, then use this formula in B2:

=IFERROR(LOOKUP(9.999E+307,SEARCH($G$2:$G$6,A2), $G$2:$G$6),"")

copied down.

 

[LadyDi]

That worked. Thank you very much.

One more question. Is there any way to know if there is more than one keyword in the comments?

 

[NBVC]

Try this, with same assumptions as above in B2:

=IFERROR(INDEX($G$2:$G$6,SMALL(IF(ISNUMBER(SEARCH($G$2:$G$6,$A2)),ROW($G$2:$G$6)-ROW($G$2)+1),COLUMNS($A$1:A$1))),"")

this is to be confirmed with CTRL+SHIFT+ENTER not just ENTER and copied across 5 columns and down.

 

[LadyDi]

That is perfect. Thank you very much.

 

[NBVC]

You are welcome