VLOOKUP and search within string

[kathleenfarber]

Hi,

I am looking for some assistance on how to vlookup and search within a string then return yes or no. I have never searched within a string and I'm completely lost.

SHEET 1 - DB EXPORT:

COLUMN A	COLUMN B	COLUMN C	COLUMN D	COLUMN E	COLUMN F
company	last name	first name	job title	email address	indications
Novar	phillips	tom	director	tom.phillips@novar.com	cf, gh, nagh, pro
Agra	cooke	karolyn	manager	karolyn.cooke@agra.com	gh, pro
Otis	stewart	mark	director	mark.stewart@otis.com	gh, nagh, pro

SHEET 2 - WORKING FILE:

COLUMN A	COLUMN B	COLUMN C
email address	indication	Yes or No
tom.phillips@novar.com	pro	yes
karolyn.cooke@agra.com	gh	yes
mark.stewart@otis.com	abg	no

I need to match sheet 2 column A to sheet 1 column E then search the indication in sheet 2 column B and find it in sheet 1 column F. If found, result is yes or no.

Your help is greatly appreciated.
~kat

 

[Special-K99]

Try (untested)
in Sheet2!D2
=IF(ISNUMBER(SEARCH(B2,VLOOKUP(A2,Sheet1!E$2:F$1000,2,0))),"Yes","No")
and copy down column D

 

[kathleenfarber]

It works with one minor issue. Thank you in advance!

I have two indications that are similiar.

NAFLD
AF

Any way to restrict one column to display a result for NAFLD and the other AF. My indication column could realistically look like NAFLD, AF, PRO, NAGH

So if an individual has NAFLD but not AF--NAFLD should be YES and AF should be NO. Right now both are reporting YES.

Thoughts?

 

[Special-K99]

Yeah thought it might do that. Thinking...

 

[Fluff]

Does this mod to Special-K99's formula sort it
=IF(ISNUMBER(SEARCH(B2&",",VLOOKUP(A2,Sheet1!E$2:F$1000,2,0)&",")),"Yes","No")

 

[kathleenfarber]

It looks like that did it. Woot! Thank you so much for all the help!!

Can you explain what the changes did to make it work?

 

[Fluff]

It adds a comma to the end of the B2 value & to the result of the Vlookup, so would be checking
AF, against NAFLD, PRO, NAGH,
Which prevents it from returning a false positive for NAFLD