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

utrolig

New Member
Joined
Sep 26, 2013
Messages
3
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:

KeywordCategorization
JavaJava
.jnlpJava
PasswordPassword

<tbody>
</tbody>

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
 
Last edited:

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,138
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:

KeywordCategorization
JavaJava
.jnlpJava
PasswordPassword

<tbody>
</tbody>

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

New Member
Joined
Sep 26, 2013
Messages
3
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:

DescriptionCategorization
user has issues with file extension .jnlpJava
User reports that Java is missingJava
user required a new password for his accountPassword

<tbody>
</tbody>

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

KeywordCategorization
.jnlpJava
javaJava
passwordPassword

<tbody>
</tbody>


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

Thanks :)

Kind regards,
utrolig
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,138
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:

DescriptionCategorization
user has issues with file extension .jnlpJava
User reports that Java is missingJava
user required a new password for his accountPassword

<tbody>
</tbody>

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

KeywordCategorization
.jnlpJava
javaJava
passwordPassword

<tbody>
</tbody>


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
 
Last edited:

utrolig

New Member
Joined
Sep 26, 2013
Messages
3
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,138
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

New Member
Joined
Dec 26, 2013
Messages
5
what does the (9.99999999999999E+307) mean in this case? I did the formula evaluate and it returned 47?

thanks
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,138

Forum statistics

Threads
1,085,307
Messages
5,382,849
Members
401,807
Latest member
xlWatcher

Some videos you may like

This Week's Hot Topics

Top