Formula to find if the text in one field is available in another field.

[pratheesh1983]

I have a data set that contains data that has field tags, and each tag has the value mentioned. We need an Excel rule to check if a name mentioned in one field exists in another field. I have tried to explain the requirements in the table below. In the last column, I need to put a formula that will populate the value as "yes" if the name is available and "no" if the name is not available.

In some cases, if the name mentioned in the second field is missing one or two words from the name mentioned in the first field, it should also populate the value as Yes (refer to row two in the table). In those cases, it should also populate the value as Yes (refer to row two in the table), and this is only applicable for names that have more than three words. Also, in some cases, the AT19 field has special characters, but in the AT20 field, the name won't have special characters. In those cases, the formula should be able to consider the name as the same as mentioned in AT19. I know the conditions look quite complex, but I would appreciate it if anyone could help me define a rule for them. Thanks in advance.

Text Value	AT19 (Field Content)	AT20 (Field Content)	AT19 NAME IS AVAIABLE IN AT20-YES OR NO (formula column)
:AT01: 20303030 :AT02: HAMMER :AT19: MARKA, ANNA-MARIA AT20: 6785.89763.5005 MARKA, ANNA-MARIA DE76673939002883 JAN	MARKA, ANNA-MARIA	6785.89763.5005 MARKA, ANNA-MARIA DE76673939002883 JAN	YES
:AT01: 45678393 :AT02: HOLLOW :AT19: MARIA BLANCA ESCOBAR ALVARO :AT20: RTE MARIA BLANCA ESCOBAR FEB2002	MARIA BLANCA ESCOBAR ALVARO	RTE MARIA BLANCA ESCOBAR FEB2002	YES
:AT01: 23783 :AT02: PIILLOW :AT19: ANTONIA-FERNANDA, RITA :AT20: RTE ANTONIA FERNANDA RITA FEB2002	ANTONIA-FERNANDA, RITA	RTE ANTONIA FERNANDA RITA FEB2002	YES
:AT01: 267833 :AT02: POR :AT19: ANTHONY, JULIAN :AT20: THEGTTS FEB2002	ANTHONY, JULIAN	THEGTTS FEB2002	NO

 

[Anfinsen]

Without seeing your actual sheet, my best guess is this

=IF(VLOOKUP(C4,A:A,1,)=C4,"YES","NO")