Find elements of text in a cell and poss vlookup against a table

[fudgeukraine]

Hope someone can help me out. I've tried a range of IF, ISNUMBER, & SEARCH but can't seem to get it working. I'm looking to do the following (it's for an Adwords search query report, in case that helps anyone)

What I'm trying to do is find instances of text keywords within a cell, and then show the category (that I'm creating in a table)

Example, the cells contain text such as
"Brand official website"
"misspelt brand free quote"
"product A buy online"
"where can I order product A to deliver free"
"looking for Product B website"
"compare prices for Product B"

I've created another table which will have a column for the Category, and then a column for all instances of the terms (variations and misspellings) that will show for this, so this can be expanded as time goes on (the report will be running over time, so needs the flexibility of a table).

I've made an example with a mock brand/product to give an example of what I'm trying to do.

Thanks!

All I really need at present, is for a formula to run against the cells of text, and then in another column, return ideally the search element it's found, and then the category in another column (if not one or the other can be VLOOKUP'd).

I'm having trouble trying to strip out the text and return the category. so hoping someone on here is able to help out.

Thanks!

[AliGW]

Welcome to the forum!

Will you please attach a sample Excel workbook? We are not able to work with or manipulate a picture of one and nobody wants to have to recreate your data from scratch.

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 results are also shown (mock up the results manually).

3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

4. Try to avoid using merged cells as 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.

Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.

[samba_ravi]

Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[fudgeukraine]

Thanks all, have attached a mock sheet.

as a note, ideally I'd have the Category/Keyword table on a seperate tab, but for this example I've just put it in the same tab.

Thanks

[AliGW]

I'm not quite clear on what you are asking. Is the table on the far right the lookup table and the table in the middle the one you wish to populate? What happens if there are two keywords in the search term?

[Bosco]

Maybe………..

1] In "Keyword" C6, copied down :

=LOOKUP(1,-SEARCH(K$6:K$17,B6),K$6:K$17)

2] In "Category" D6, copied down :

=LOOKUP(1,-SEARCH(K$6:K$17,B6),J$6:J$17)

Regards
Bosco

[fudgeukraine]

Hi AliGW.

The table on the left is going to be the data I want to review (column B is the element I want to search against)

The table on the right is the list of elements I want it to find against, and the category that each sits under (basically, I've got a list of 1,000's of keywords and I want to scan them for the different categories they may be under, such as brand terms, products, markets etc, and this should avoid doing it manually). This will be added to continually; if there's new categories, keyword elements etc, then I'll add them in at the bottom and resort

The formula should scan the keywords in column B and find any instances of this in column K, and then return the Keyword element and Category (i.e. if it contains the brand term, it'll return 'brand', if it's product A, it'll return 'product A'.

Putting the keyword part in Column C isn't necessary - it's the Category I want, but if the keyword can be shown then that's useful to have.

Good point re: 2+ instances of this as not something I'd considered. Could it return multiple categories in different columns (e.g. Keyword contains elements from Category A, B, Brand and so on?) or would that cause issues?


The 'keyword'

[samba_ravi]

C6=LOOKUP(2,1/SEARCH(K$6:INDEX(K:K,MATCH("ZZ",K:K)),$B6),K$6:INDEX(K:K,MATCH("ZZ",K:K)))
D6=LOOKUP(2,1/SEARCH(K$6:INDEX(K:K,MATCH("ZZ",K:K)),$B6),J$6:INDEX(J:J,MATCH("ZZ",K:K)))
Try this and copy towards down

[fudgeukraine]

Thanks Bosco & nflsales! Both of those formulas seem to do the core element of what I need, appreciate it!

Do you have any thoughts on how I could flag up instances of 2+ category keywords showing up.

e.g. if the search term is "ABC widget buy online that would come under Brand & Product A. In both of your formulas, it shows up as Product A, but would it be possible to show it as both Brand and Product A (maybe in an additional column? or is that just complicating things?

Thanks

[samba_ravi]

C6=

Please Login or Register  to view this content.

Try this and copy paste across C6:F12

[fudgeukraine]

Thanks Nflsales!

Thanks for this; will this show the 'brand' element and then any other category, or does it show anything else? I've tested it and can see it shows a Brand and Product A (if I duplicate the columns), but not sure if there's any other parts I should know?