Formula for looking up keywords and entering text in another column if a certain keyword is found.

[utrolig]

Hi guys!

I need a formula or a script which will check a if a cell contains keywords from a list in another sheet.
If a keyword is found, it will populate the cell next to it with the "category" set for that keyword.

So I have a sheet which is called "Keywords" which contains the keywords to look for, and the categorization for each keyword.
Example:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Keyword[/TD]
[TD]Categorization[/TD]
[/TR]
[TR]
[TD]Java[/TD]
[TD]Java[/TD]
[/TR]
[TR]
[TD].jnlp[/TD]
[TD]Java[/TD]
[/TR]
[TR]
[TD]Password[/TD]
[TD]Password[/TD]
[/TR]
</tbody>[/TABLE]

So basically what I want is a formula that can check "A1" if the cell contains any of the keywords from the "Keywords" sheet, and if it does - populate "B1" with the corresponding categorization depending on what keyword was found.

Is this even possible in Excel?

Kind regards,
utrolig

 

[Andrew Poulsom]

Welcome to MrExcel.

It appears that you can use VLOOKUP:

http://www.contextures.com/xlFunctions02.html

 

[Aladin Akyurek]

Hi guys!

I need a formula or a script which will check a if a cell contains keywords from a list in another sheet.
If a keyword is found, it will populate the cell next to it with the "category" set for that keyword.

So I have a sheet which is called "Keywords" which contains the keywords to look for, and the categorization for each keyword.
Example:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Keyword[/TD]
[TD]Categorization[/TD]
[/TR]
[TR]
[TD]Java[/TD]
[TD]Java[/TD]
[/TR]
[TR]
[TD].jnlp[/TD]
[TD]Java[/TD]
[/TR]
[TR]
[TD]Password[/TD]
[TD]Password[/TD]
[/TR]
</tbody>[/TABLE]

So basically what I want is a formula that can check "A1" if the cell contains any of the keywords from the "Keywords" sheet, and if it does - populate "B1" with the corresponding categorization depending on what keyword was found.

Is this even possible in Excel?

Kind regards,
utrolig

Try...

=LOOKUP(9.99999999999999E+307,SEARCH(KeyWords,A1),Categories)

KeyWords stands for the range housing the relevant key words and Categories for the range housing the relevant categorizations.

 

[utrolig]

Thank you for the swift responses! I think I need to provide a better example.

The Keyword Sheet has the information which will be populated in B2.
I want the formula i input in B2 to search A2 for all keywords which is in the list in the keyword sheet. If it finds one of the keywords, it will enter the relevant Categorization value.

Example:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Description[/TD]
[TD]Categorization[/TD]
[/TR]
[TR]
[TD]user has issues with file extension .jnlp[/TD]
[TD]Java[/TD]
[/TR]
[TR]
[TD]User reports that Java is missing[/TD]
[TD]Java[/TD]
[/TR]
[TR]
[TD]user required a new password for his account[/TD]
[TD]Password[/TD]
[/TR]
</tbody>[/TABLE]

The Categorization is populated from the Keyword list in the sheet called "Keywords".
The list looks like this:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Keyword[/TD]
[TD]Categorization[/TD]
[/TR]
[TR]
[TD].jnlp[/TD]
[TD]Java[/TD]
[/TR]
[TR]
[TD]java[/TD]
[TD]Java[/TD]
[/TR]
[TR]
[TD]password[/TD]
[TD]Password[/TD]
[/TR]
</tbody>[/TABLE]


I have never used advanced formulas like this, so I appreciate all the help I can get!

Thanks

Kind regards,
utrolig

 

[Aladin Akyurek]

Thank you for the swift responses! I think I need to provide a better example.

The Keyword Sheet has the information which will be populated in B2.
I want the formula i input in B2 to search A2 for all keywords which is in the list in the keyword sheet. If it finds one of the keywords, it will enter the relevant Categorization value.

Example:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Description[/TD]
[TD]Categorization[/TD]
[/TR]
[TR]
[TD]user has issues with file extension .jnlp[/TD]
[TD]Java[/TD]
[/TR]
[TR]
[TD]User reports that Java is missing[/TD]
[TD]Java[/TD]
[/TR]
[TR]
[TD]user required a new password for his account[/TD]
[TD]Password[/TD]
[/TR]
</tbody>[/TABLE]

The Categorization is populated from the Keyword list in the sheet called "Keywords".
The list looks like this:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Keyword[/TD]
[TD]Categorization[/TD]
[/TR]
[TR]
[TD].jnlp[/TD]
[TD]Java[/TD]
[/TR]
[TR]
[TD]java[/TD]
[TD]Java[/TD]
[/TR]
[TR]
[TD]password[/TD]
[TD]Password[/TD]
[/TR]
</tbody>[/TABLE]


I have never used advanced formulas like this, so I appreciate all the help I can get!

Thanks

Kind regards,
utrolig

Let A1:B4 on Sheet1 house the data and expected results.

Let A1:B4 on Sheet2 house the keywords and the categorizations. Select A2:A4 and name this range as KeyWords; Select B2:B4 and name this range Categories or Categorizations.

Now in B2 on Sheet1 enter and copy down:

=LOOKUP(9.99999999999999E+307,SEARCH(KeyWords,A2),Categories)

See:

https://dl.dropboxusercontent.com/u/65698317/aaLookupSearch utrolig.xlsx

 

[utrolig]

Let A1:B4 on Sheet1 house the data and expected results.

Let A1:B4 on Sheet2 house the keywords and the categorizations. Select A2:A4 and name this range as KeyWords; Select B2:B4 and name this range Categories or Categorizations.

Now in B2 on Sheet1 enter and copy down:

=LOOKUP(9.99999999999999E+307,SEARCH(KeyWords,A2),Categories)

See:

https://dl.dropboxusercontent.com/u/65698317/aaLookupSearch utrolig.xlsx

Thank you very much!

The whole office is celebrating this magnificent formula! There will be cake.

Again, thank you!

Regards,
utrolig

 

[Aladin Akyurek]

Thank you very much!

The whole office is celebrating this magnificent formula! There will be cake.

Again, thank you!

Regards,
utrolig

Great. You are welcome.

 

[Flyingcomet]

what does the (9.99999999999999E+307) mean in this case? I did the formula evaluate and it returned 47?

thanks

 

[Aladin Akyurek]

what does the (9.99999999999999E+307) mean in this case? I did the formula evaluate and it returned 47?

thanks

The posts #13 and #5 in:

http://www.mrexcel.com/forum/excel-questions/724843-there-case-sensitive-vlookup.html
http://www.mrexcel.com/forum/excel-questions/654593-using-vlookup-find-string-text.html

respectively, explain the working of the formula (with FIND, a kindred function).

The latter contains additional, relevant links.

 

[Flyingcomet]

Thank you!