# Thread: Search for multiple text strings simultaneously in a single cell Thanks: 0 Likes: 0

1. ## Re: Search for multiple text strings simultaneously in a single cell

Originally Posted by laviz
Hi, the below formula is really helpful.
Question - is there a way to bring the word that was found, instead of "Yes" / "No"?
thanks a lot!
Try...

=LOOKUP(9.99999999999999E+307,SEARCH(KeywordTable,E2),KeywordTable)

2. ## Re: Search for multiple text strings simultaneously in a single cell

WOWWWWW excellent, thanks a lot!

3. ## Re: Search for multiple text strings simultaneously in a single cell

Originally Posted by laviz
WOWWWWW excellent, thanks a lot!
You are welcome.

4. ## Re: Search for multiple text strings simultaneously in a single cell

Originally Posted by Marcelo Branco
An example

 A B C D 1 Text Keyword Keywords 2 text key3 text Key3 Key1 3 key4 text Key4 Key2 4 text key1 Key1 Key3 5 text text Not Found Key4 6 text key2 key4 Key2 Key5

D2:D6 is a named range --> Keywords

Array formula in B2 copied down

Confirmed with Crtl+Shift+Enter

Remark: If the cell contains more than one keyword (like A6) the formula returns just the first keyword found.

Hope this helps

M.

This is almost exactly as I need too, so thank you very much for the reply.

If I needed the return to detail all keywords found not just the first, could it be done? ie for row A6 in example given the return would be a cell with both key numbers in a cell comma separated perhaps? (key2, key4)

5. ## Re: Search for multiple text strings simultaneously in a single cell

Originally Posted by mickarose
This is almost exactly as I need too, so thank you very much for the reply.

If I needed the return to detail all keywords found not just the first, could it be done? ie for row A6 in example given the return would be a cell with both key numbers in a cell comma separated perhaps? (key2, key4)
That can be done either each return in a cell of its own or comma-separated in a single cell using ACONCAT, a function in VBA.

6. ## Re: Search for multiple text strings simultaneously in a single cell

Try...

=LOOKUP(9.99999999999999E+307,SEARCH(KeywordTable,E2),KeywordTable)
I joined the forum just to say THANK YOU!!

7. ## Re: Search for multiple text strings simultaneously in a single cell

Originally Posted by aquariaj1
I joined the forum just to say THANK YOU!!
You are welcome.

8. ## Re: Search for multiple text strings simultaneously in a single cell

Originally Posted by Marcelo Branco
An example

 A B C D 1 Text Keyword Keywords 2 text key3 text Key3 Key1 3 key4 text Key4 Key2 4 text key1 Key1 Key3 5 text text Not Found Key4 6 text key2 key4 Key2 Key5

D2:D6 is a named range --> Keywords

Array formula in B2 copied down

Confirmed with Crtl+Shift+Enter

Remark: If the cell contains more than one keyword (like A6) the formula returns just the first keyword found.

Hope this helps

M.
Thank you! Works like a charm!

One more question:

How can it be done with another column: E(Answers) ?

E has answers for keywords. So, IF A HAS TEXT OF D(Keywords) THEN COPY THE E(Answers) INTO B

9. ## Re: Search for multiple text strings simultaneously in a single cell

Originally Posted by macci
Thank you! Works like a charm!

One more question:

How can it be done with another column: E(Answers) ?

E has answers for keywords. So, IF A HAS TEXT OF D(Keywords) THEN COPY THE E(Answers) INTO B

In B2 control+shift+enter, not just enter, and copy down:

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•