Find Keyword in URL from Lookup Table

**newbieexceldude** · 10-07-2016, 08:25 PM

Hi Everyone,

I've been wracking my head trying to figure this out.

Scenario (steps)
1. For a list of URLs in Column A, I need to search that list for a specific keyword
2. The keyword being searched for is a list/table in Columns B. There will probably be 10 - 15 keywords
3. In Column C, I need to return the keyword if found. If not, either the cell in Column C is blank or returns "not found"

Sample URL List
http://www.groovyclothing.com/cashmere-travel-wrap-750
http://www.groovyclothing.com/cashme...inge-poncho-98
http://www.groovyclothing.com/weathe...hmere-scarf-47
http://www.groovyclothing.com/essent...travel-wrap-22
http://www.groovyclothing.com/essent...-sweatshirt-69
http://www.groovyclothing.com/the-la...shmere-shampoo
http://www.groovyclothing.com/spring...travel-wrap-34

For example, the keyword is "cashmere" (Column B) and I'm looking to see it is found in the URL list (Column A).

I'm thinking that a complex formula using SEARCH and FIND might work? But I think the problem in this method is the URL syntax (colon, slashes, dashes) and also that the keyword is not found in the same position in each URL.

So, just wondering if this can be done by formula or if VBA is required?

Thanks in advanced!

**zbor** · 10-08-2016, 02:35 AM

What if in http://www.groovyclothing.com/spring...travel-wrap-34 it find three keywords: spring, travel and wrap?
What you want as output?

**zbor** · 10-08-2016, 02:35 AM

What if in http://www.groovyclothing.com/spring...travel-wrap-34 it find three keywords: spring, travel and wrap?
What you want as output?

**Glenn Kennedy** · 10-08-2016, 03:21 AM

Will you please attach a sample Excel workbook? Please don't attach a picture of one.

1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired solution is also shown (mock up the results manually).

3. Make sure that all confidential information is removed first!!

4. Try to avoid using merged cells. They cause lots of problems!

Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

**Tony Valko** · 10-08-2016, 04:49 AM

Try this...

Data in the range A2:An

Keyword list in the range B2:B10

Enter this formula in C2 and copy down as needed:

=IFERROR(LOOKUP(5000,SEARCH(B$2:B$10,A2),B$2:B$10),"")

**Tony Valko** · 10-08-2016, 04:53 AM

P.S.

You don't have what version of Excel you're using in your profile.

I've assumed Excel 2007 or later.

**newbieexceldude** · 10-08-2016, 05:43 PM

Thanks for responding, Zbor.

Ideally, it would list all words found (space separated) by the order that the word is found in the lookup list.

So if this is the lookup list:
B1: Cashmere
B2: Travel
B3: Wrap

The output in the cell (Column C) would be: cashmere travel wrap.

Otherwise, if it's only possible to output 1 word, it would be the 1st word in the list (cashmere)

**newbieexceldude** · 10-08-2016, 05:57 PM

Thanks for responding, Glenn.

I'm hoping that I attached the file properly

**newbieexceldude** · 10-08-2016, 05:58 PM

Thanks Tony. Unfortunately, the only thing returned to Column C were zeros.

I am using Office 365 ProPlus.

**Tony Valko** · 10-08-2016, 07:10 PM

I looked at your file.

If you want multiple keywords returned to a single cell it will require a VBA function.

**newbieexceldude** · 10-08-2016, 11:08 PM

Hi Everyone,

I originally posted this in the formulas section, but one of the kind souls that responded said VBA would be required to get my desired output.

Scenario (steps)
1. For a list of URLs in Column A, I need to search that list for a specific keyword
2. The keyword being searched for is a list/table in Columns B. There will probably be 10 - 15 keywords
3. In Column C, I need to return the keyword if found. If not, either the cell in Column C is blank or returns "not found"

Ideally, it would list all words found (space separated) by the order that the word is found in the lookup list.

So if this is the lookup list:
B1: Cashmere
B2: Travel
B3: Wrap

The output in the cell (Column C) would be: cashmere travel wrap.

Otherwise, if it's only possible to output 1 word, it would be the 1st word in the list (cashmere)

Thanks in advanced!

**karedog** · 10-09-2016, 12:41 AM

Maybe :

Please Login or Register  to view this content.

**newbieexceldude** · 10-09-2016, 01:22 AM

Thanks Karedog! Great work. I really appreciate you for taking the time to put this together. It's 99% of what I was looking for.

The remaining 1% is that it only works on exact match. It doesn't match singular to plural (you have to put both in to get matches for both). So I'm thinking that it's probably best to set the exact match on singular and have that to include singular and plural forms of the word.

Is there a way to modify to make this happen.

Thanks so much again for your help.

**karedog** · 10-09-2016, 01:46 AM

You are welcome.

That would be need a "database solution" matching work, since there are so many irregular plurals noun (can't be solved by using mathematical algorithm alone).

For example : (https://en.wikipedia.org/wiki/English_plurals)

hero --> heroes
potato --> potatoes

but :
zero --> zeros (not zeroes)
piano --> pianos (not pianoes)

also many words that totally changed for plural, like foot --> feet

So I don't think it will be possible without using "database solution".

**zbor** · 10-09-2016, 02:10 AM

Here, I've create UDF called FIND_KEYWORDS.
Use it as:

Formula:

Please Login or Register  to view this content.

Where B3 is http link that you looking for
C3:C8 is range with your keywords. Of course it can be changed.

What can't be changed is structure of your B3 link.
Keywords must be in last word separater by /
i.e. OK http://www.groovyclothing.com/essential-cashmere-striped-travel-wrap-22
NOT OK http://www.groovyclothing.com/essent...travel-wrap-22/

And in that word after last / it will look for - separated words:
i.e. OK essential-cashmere-striped-travel-wrap-22
NOT OK essential_cashmere_striped_travel_wrap_22

**zbor** · 10-09-2016, 02:14 AM

This is a duplicate post and as such does not comply with Rule 5 of our forum rules.

Due to provided solution this thread is now merged with original thread.

**newbieexceldude** · 10-09-2016, 12:11 PM

Thanks for the UDF, zbor. It's really good work.

Sorry about the rules violation.

Find Keyword in URL from Lookup Table

LinkBack

Thread Tools

Rate This Thread

Display

Find Keyword in URL from Lookup Table

Re: Find Keyword in URL from Lookup Table

Re: Find Keyword in URL from Lookup Table

Re: Find Keyword in URL from Lookup Table

Re: Find Keyword in URL from Lookup Table

Re: Find Keyword in URL from Lookup Table

Re: Find Keyword in URL from Lookup Table

Re: Find Keyword in URL from Lookup Table

Re: Find Keyword in URL from Lookup Table

Re: Find Keyword in URL from Lookup Table

A Macro to Find Keyword in URL from Lookup Table

Re: A Macro to Find Keyword in URL from Lookup Table

Re: A Macro to Find Keyword in URL from Lookup Table

Re: A Macro to Find Keyword in URL from Lookup Table

Re: Find Keyword in URL from Lookup Table

Re: A Macro to Find Keyword in URL from Lookup Table

Re: A Macro to Find Keyword in URL from Lookup Table

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Find if keyword is in the URL (which contains no spaces)

How to lookup a keyword and list the content? Thanks.

Keyword Lookup in Google and Return URL

Copy keyword from one sheet, find that keyword in cells in column D and paste it under rel

lookup or search text in sentence that match with keyword

Excel 2007 : Find table datat by lookup of row & column

Bookmarks

Bookmarks

Posting Permissions