Search Function

T

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
 
P

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
 
T

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Excel - Wich Function should I use? 1
Search cell value in same column 3
VBA Coding Help for Beginner 0
Combining Text From Cells 3
Find a value in table 2
If function..please help 1
Key Words 3
Transpose 1

Top