• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Categorization based on keywords in a string

A

ajgodson

New Member
Hi all - I could use help with the following problem.


In column A, I have a string of words. (e.g. Senior Process Engineer - Plano, TX)


In sheet 2, column B through column D, I have up to 3 keywords the help me categorize the string and in column E, I have the name of the category. (e.g, Columns B-D, "engineering", "engineer", "CPE". Column E, "Engineering")


I have about 25 categories, thus about 25 rows.


What formula or Macro can I write that essentially says, "If the keywords in columns B-D appear in the string in column A, return the value from column E"


I feel like I've been close with a series of formulas, but haven't been able to figure it out.


Any help would be greatly appreciated. If I can clarify in any way, please let me know. Thanks!
 
Hi, ajgodson!

First of all welcome to Chandoo's website Excel forums. Thank you for your joining us and glad to have you here.


You have not specified where do you want Value of E..

I guess If A1 contain CPE, then you want Value of E in Column D..


So try, beloW formula in B2, and copy the same to B2 to D25.


Code: 
=IFERROR(IF(SEARCH(B$1,$A2)>0,$E2),"")


Please let us know, if you are looking for something else.. or paste your sample data with requirement..


for detail, visit below URL.

http://chandoo.org/forums/topic/posting-a-sample-workbook


Regards,

Deb
 
This will work if E1 contains your "value not found" message.

Array formula:

=INDEX(E:E,MAX(ISNUMBER(SEARCH(B2:D25,A2))*ROW(B2:D25)*(D2:D25<>"")))


Remeber to confirm formula using Ctrl+Shift+Enter not just Enter. In the formula, the B2:B10 array can be adjusted to fit the size of your actual data.
 
OOPS.. my mistake :( Sorry..

I have not read the post carefully..


Luke's formula is working perfect, with some normal modification..

use the below formula in Sheet 1 > B2. and drag as per requirement..

Code: 
{=IFERROR(INDEX(Sheet2!$E$2:$E$25,MAX(ISNUMBER(SEARCH(Sheet2!$B$2:$D$25,A2))*ROW(Sheet2!$B$2:$D$25)-1)),"Not Found")}


I am assuming you have filled category Text in Sheet2 > B2 to D25..


Thanks Luke..


Regards,

Deb
 
Hi Luke & Deb -


First, a HUGE thanks! It put a big smile on my face on Friday night to see two intelligent responses within mere hours of posting.


I feel like I'm close, but I keep getting the last result as the return instead of the result from the row. It's probably something simple I'm doing, but I'm hoping you could take a peek and see if there's something you'd recommend.


https://www.dropbox.com/s/aabksrgvf6iyucn/JM%20-%20Excel%20Help.xlsx


Many thanks for your help with this!


Adam
 
Hi Adam,

I think Deb and Luke interpreted your question as wanting to search the whole column, and not just the current row. If you are looking for a value based on just a given row, please try the following formula:


=IF(SUMPRODUCT(N(ISNUMBER(SEARCH(B1:D1, A1))))>0,E1, "Not found")


Copy it to additional rows.


Cheers,

Sajan.
 
Hi Sajan - Thanks for the response! It is very much appreciated. I'm not 100% sure that gets me to where I want to be, as I need to search the string in column A for all the keywords and if it is found, then return the value in column E of whatever row the keyword was found in.


If it's user error, feel free to point that out, too. Thx!


Adam
 
ajgodson,


Here's the sample data I created, and formula output

[pre]
Code: 
A                B        C                 D             E                F
String	      Keyword1	Keyword2	Keyword3	No Value     FormulaOutput
I am a fox	cat	cry		                c-words		f-words
I am a cat	dog			                d-words		c-words
I am dog	frog	fox	         fly             f-words	d-words
I am nothing						                No Value
[/pre]
Array formula in F2 is:

=INDEX(E:E,MAX(ISNUMBER(SEARCH($B$2:$D$25,A2))*ROW($B$2:$D$25)*($B$2:$D$25<>"")))


Note that I did change my original formula a little to include absolute references, and corrected the last array to be b2:d25 instead of d2:d25. If the above output is not what you are looking for, can you correct me? Thanks.
 
Hurrah! Glad I was able to clear things up, and to hear that things are working okay now. =)
 
You must log in or register to reply here.
Back
Top