Search Function

[TerryR]

I have been using the Excel SEARCH Function to search for a specifie
value in a text string in a cell. However, I'd like to be able t
search that cell's contents to see if it contains one of a list o
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?

Thank

 

[Pete_UK]

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

 

[TerryR]

Hi Pete,

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

 

[Pete_UK]

You're welcome - thanks for feeding back.

Pete