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

pratheesh1983

Board Regular
Joined
Aug 13, 2015
Messages
55
Office Version
  1. 365
Platform
  1. Windows
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 ValueAT19
(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
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
Without seeing your actual sheet, my best guess is this

Excel Formula:
=IF(VLOOKUP(C4,A:A,1,)=C4,"YES","NO")
 
Upvote 0

Forum statistics

Threads
1,223,243
Messages
6,170,967
Members
452,371
Latest member
Frana

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top