Search for multiple text strings simultaneously in a single cell

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,170
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)
 

Some videos you may like

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

mickarose

New Member
Joined
Aug 6, 2015
Messages
1
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

=IFERROR(INDEX(Keywords,MATCH(TRUE,ISNUMBER(SEARCH(" "&Keywords&" "," "&A2&" ")),0)),"Not Found")

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)
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,170
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.
 

macci

New Member
Joined
Oct 20, 2015
Messages
2
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​

<tbody>
</tbody>


D2:D6 is a named range --> Keywords

Array formula in B2 copied down

=IFERROR(INDEX(Keywords,MATCH(TRUE,ISNUMBER(SEARCH(" "&Keywords&" "," "&A2&" ")),0)),"Not Found")

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


A
B
C
D
E
1
Text​
Answer
Keywords​
Answers​
2
text key3 text​
Answer3
Key1​
Answer1
3
key4 text​
Answer4
Key2​
Answer2​
4
text key1​
Answer1
Key3​
Answer3​
5
text text​
Not Found
Key4​
Answer4​
6
text key2 key4​
Answer2
Key5​
Answer5

<tbody>
</tbody>
 
Last edited:

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,170
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


A
B
C
D
E
1
Text​
Answer
Keywords​
Answers​
2
text key3 text​
Answer3
Key1​
Answer1
3
key4 text​
Answer4
Key2​
Answer2​
4
text key1​
Answer1
Key3​
Answer3​
5
text text​
Not Found
Key4​
Answer4​
6
text key2 key4​
Answer2
Key5​
Answer5

<tbody>
</tbody>
In B2 control+shift+enter, not just enter, and copy down:

=IFERROR(INDEX($E$2:$E$6,MATCH(TRUE,ISNUMBER(SEARCH(" "&$D$2:$D$6&" "," "&A2&" ")),0)),"Not Found")
 

Watch MrExcel Video

Forum statistics

Threads
1,096,324
Messages
5,449,720
Members
405,575
Latest member
Masimo85

This Week's Hot Topics

Top