VLOOKUP "Contains text"

[Stephen]

Hi All,

How can I get VLOOKUP to find a particular word within a line of text.

For example A1= "Peter, Mark, Stephen" and B1="10". How can I ge
VLOOKUP to look for "Mark" and result in "10"?

Please help.

Stephe

 

[BrianB]

This formula works assuming no spaces in A1 and lookup table named rang
"MyLookup". It works by finding the 2 commas and taking whatever i
between.

=VLOOKUP(MID(A1,FIND(",",A1,1)+1,FIND(",",A1,FIND(",",A1,1)+1)-FIND(",",A1,1)-1),MyLookup,B1,FALSE

 

[JMay]

Not quite sure what your asking to do here,
but here is a direction to perhaps take,
modify to suit your needs.

=IF(FIND("Mark",A1),B1,"") ' If(True,do something, otherwiseshowblank)