Need an Excel Formula to find columns with certain key words?

[ltn123]

Lets say Column A is a list of URLs. Column B is a list of keywords.

I need a formula where if any of the keywords in Column B is found within the url structure in A it spits out the URL is column C.

Thanks

 

[Scott Huish]

=VLOOKUP("*"&B1&"*",A:C,3,0)

Copy down.

 

[MarcelBeug]

If data starts on row 2, enter in C2 and confirm with CTRL-SHIFT-ENTER:

=IF(OR(NOT(ISERROR(SEARCH(B:B,A2)))),A2,"")

And copy down.

 

[ltn123]

=VLOOKUP("*"&B1&"*",A:C,3,0)

Copy down.

I can't get this to work.

If data starts on row 2, enter in C2 and confirm with CTRL-SHIFT-ENTER:

=IF(OR(NOT(ISERROR(SEARCH(B:B,A2)))),A2,"")

And copy down.

This works kind of..The formula is not looking for any of the keywords in Column B just at that one keyword in that cell. I need the formula for any of the keywords in column B and spit out the URLs from Column A that have any of the keywords on to column C.

 

[ltn123]

This is what I am looking for

URL Keywords URL found with a keyword in it Stroke_patients_maintain_benefits_of_robot_therapy.aspx girlfriend Government_fights_over_*******_employees_benefits.aspx <tbody> </tbody> cancer Seatbelts_airbags_also_good_for_kidneys.aspx <tbody> </tbody> stroke Soul/Horoscope/Cancer/2014/20140819/Cancer.aspx <tbody> </tbody> government Health_officials_tell_Greece_to_act_fast_to_control_HIV.aspx <tbody> </tbody> fights Articles/Q/QA_Should_I_Stand_By_My_Ex-Druggie_Girlfriend.aspx <tbody> </tbody> also <tbody> </tbody>

<colgroup><col><col><col></colgroup><tbody>
</tbody>

 

[MarcelBeug]

It works with me. I guess you did not confirm the formula with CTRL-SHIFT-ENTER, causing it to be surrounded by {}, like:

{=IF(OR(NOT(ISERROR(SEARCH(B:B,A2)))),A2,"")}

 

[Scott Huish]

Without CTRL-SHIFT-ENTER:

=IFERROR(INDEX(A:A,MATCH("*"&B1&"*",A:A,0)),"")

 

[ltn123]

Without CTRL-SHIFT-ENTER:

=IFERROR(INDEX(A:A,MATCH("*"&B1&"*",A:A,0)),"")

This work perfectly.

One thing I find weird with excel. Why can't I use commas in formulas

this doesn't work

=IFERROR(INDEX(A:A,MATCH("*"&B1&"*",A:A,0)),"")


but this does

=IFERROR(INDEX(A:A|MATCH("*"&B1&"*"|A:A|0))|"")

 

[Scott Huish]

Have you changed anything in your Windows Regional Settings?

 

[ltn123]

Have you changed anything in your Windows Regional Settings?

Haven't touched those options unless the IT dept did. I'll need to ask them . Unless you know how to fix.

Also, I spoke to soon about that formula. Seems like it only grabs the first URL it finds not every single URL that a keyword.