How to return proper names if mentioned in an open comment box

[danducati]

I am trying to return proper names that are included in open comment boxes so these people can be recognized.

Formula Sought: If the comment box includes a proper name that matches a name in the named range called kudos, then I want that name to be returned in the "CS Rep Recognized" column. If there is no match, the cell would show as blank.

Current Formula, which is close but not exact: =IFERROR(INDEX(kudos,MATCH(TRUE,ISNUMBER(SEARCH(kudos,M6909)),0)),"")

I have gotten very close as you will see below with the "Alex" example. But it's not the perfect formula as you can see in the "Lea" example, which is pulling Lea back because of the word "clear" in the comment box. I would be so grateful if someone could help me get over the finish line with this. Thank you!!!!!!!!!!

Named Range "kudos"

 

[Domenic]

Try...

=IFERROR(INDEX(kudos,MATCH(TRUE,ISNUMBER(SEARCH(" "&kudos&" "," "&M6909&" ")),0)),"")

Although the formula would need to be amended if a sentence in Column M ends with a name and then is immediately followed by a period.

Hope this helps!

 

[danducati]

Try...

=IFERROR(INDEX(kudos,MATCH(TRUE,ISNUMBER(SEARCH(" "&kudos&" "," "&M6909&" ")),0)),"")

Although the formula would need to be amended if a sentence in Column M ends with a name and then is immediately followed by a period.

Hope this helps!

Dude, YOU.ARE.THE.MAN!!!!!!!!!!!!!!! Thank you!!

It gets me 99% of the way there which is as good as it's going to get because "will not..." returns an employee's name Will & there is no way around that. I'll just spot check those names that could go either way & delete the formula in that cell if necessary.

I can't thank you enough, Domenic. You are AMAZING!!!

 

[Domenic]

. . . "will not..." returns an employee's name Will & there is no way around that

How about if you made the search case-sensitive by replacing SEARCH with FIND?

 

[danducati]

How about if you made the search case-sensitive by replacing SEARCH with FIND?

Great idea, I will try that. Thank you!