using ISNUMBER(SEARCH()) with a list and displaying the results.

[Mat87]

Any idea how I can apply the following formula to a range of cells?

Please Login or Register  to view this content.

The above formula check if a cell contain a specific keyword and return back the keyword if the value is true. I'm looking for a similar formula that will search multiple keywords in range and return back the matched keyword.

Thanks.

[Phuocam]

Try this ...

=LOOKUP(2,1/SEARCH({"*","keyword1","keyword2"}, "text to search within"),{"","keyword1","keyword2"})

[Mat87]

Try this ...

=LOOKUP(2,1/SEARCH({"*","keyword1","keyword2"}, "text to search within"),{"","keyword1","keyword2"})

Thanks, this do the job but is there a way to apply it to a range?

[Phuocam]

"text to search within" in A1.

List in E2:E4

=LOOKUP(2,1/SEARCH($E$2:$E$4, A1),$E$2:$E$4)

[Mat87]

"text to search within" in A1.

List in E2:E4

=LOOKUP(2,1/SEARCH($E$2:$E$4, A1),$E$2:$E$4)

Hey again, what if the cell contain two keywords or more?

Say if I had "JJAALLBB" in cell A4, is there a way to return AABB? (the current formula return BB only)