I have been using the Excel SEARCH Function to search for a specified
value in a text string in a cell. However, I'd like to be able to
search that cell's contents to see if it contains one of a list of
values I have in a separate table. For example:

a1: "The quick brown"
a2: "fox jumps"
a3: "over the"
a4: "lazy toad"

My lookup table in the next column would contain

b1: "quick"
b2: "over"
b3: "toad"

I want my search results to come back with:

a1: True (contains quick)
a2: False (does not contain quick, over or toad)
a3: True (contains over)
a4: True (contains toad)

Any ideas on how I might do this kind of reverse array lookup?

Thanks


--
TerryR
------------------------------------------------------------------------
TerryR's Profile: http://www.thecodecage.com/forumz/member.php?userid=132
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=64957

Put this in C1:

=AND(1,SUMPRODUCT(ISNUMBER(SEARCH(B$1:B$3,A1))*1))

and copy down to C4, to give you:

TRUE
FALSE
TRUE
TRUE

as requested.

Here's a slightly shorter version:

=SUMPRODUCT(ISNUMBER(SEARCH(B$1:B$3,A1))*1)=1

Hope this helps.

Pete

On Feb 16, 4:17*pm, TerryR wrote:
I have been using the Excel SEARCH Function to search for a specified
value in a text string in a cell. *However, I'd like to be able to
search that cell's contents to see if it contains one of a list of
values I have in a separate table. *For example:

a1: "The quick brown"
a2: "fox jumps"
a3: "over the"
a4: "lazy toad"

My lookup table in the next column would contain

b1: "quick"
b2: "over"
b3: "toad"

I want my search results to come back with:

a1: True (contains quick)
a2: False (does not contain quick, over or toad)
a3: True (contains over)
a4: True (contains toad)

Any ideas on how I might do this kind of reverse array lookup?

Thanks

--
TerryR
------------------------------------------------------------------------
TerryR's Profile:http://www.thecodecage.com/forumz/member.php?userid=132
View this thread:http://www.thecodecage.com/forumz/sh...ad.php?t=64957

Hi Pete,

I've tried your suggestion on a couple of simple examples and it works
perfectly. Still have to run it through the main workbook, but it's
looking good and thanks a lot for your help


--
TerryR
------------------------------------------------------------------------
TerryR's Profile: http://www.thecodecage.com/forumz/member.php?userid=132
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=64957

You're welcome - thanks for feeding back.

Pete

On Feb 17, 8:32*am, TerryR wrote:
Hi Pete,

I've tried your suggestion on a couple of simple examples and it works
perfectly. *Still have to run it through the main workbook, but it's
looking good and thanks a lot for your help

--
TerryR
------------------------------------------------------------------------
TerryR's Profile:http://www.thecodecage.com/forumz/member.php?userid=132
View this thread:http://www.thecodecage.com/forumz/sh...ad.php?t=64957