How to look-up text strings and match it to a specific item type

Hello,

I am not even sure if this is possible but if anyone knows of a way to accomplish this, I would really appreciate your help. This is for childrens clothing.
So attached is the table and interested in knowing if there is a way or formula I could use so that it will look up column "A" for string of key words in cells such as "girls, Christmas, boys, baby boys overalls" then reference column "D".
If the lookup finds these any of these words or combination of in cell A1 the words "girls, christmas, dress", The formula will then compare whats in column "D" and if "girls,dresses, special occasion" are found, then it would take from the information from " item_type" column "E" and paste the corresponding item_type word, "special-occasion-dresses" and place it into column "B2" etc. Hope this is not too confusing?

See attached and thanks for any assistance.

View attachment categories_item_type.xlsx

Hi,
Pl check attachment file. is it solve your problem ?

Hi Sambit, thank you once again for your help. I am testing out your formula and so far it's very close to what I am looking for. Out of curiosity is there a way to have excel search through two or three words within a cell and match it to a specific value?

IE:
Lookup column A
cell A1 contains "girls, dress"
A2 contains "girls, hairbow"
A3. contains. "Boys, neckties"

Returns in column B
b1: clothing
b2: accessories
B3 acessorie


reference table bled will have the following:
c1. Contains. "girls hairbow". D1 retuns "accessorie"
c2. Contains. "gitls, dress". D2 returns "clothing"
c3. Contains "Boys, neckties" D3 returns "accessorie"

Hope this is not to confusing, but I just need to know if excel is capable of referencing one or two words or more within a cell and be able to match it with a certain value or word, or is excel only able to reference only one word within a cell at a time?

Thanks you once again for your time and assistance.

Hi,
From above example, dress comes under cloth category and hairbow and neckties comes under accessories category. for which I have create helper coloum. Then I put the formula by using helper coloum. pl find attachment.

If you looking alternative then please wait somebody has replied...........................!

Sambit,

I was actually interested and sorry if I was not clearer; my fault. I wanted to know if there is a formula which could look up two or more words separated by a "," in ONE cell and be able to give it a value.
as:
IE:
Lookup column A
cell A1 contains "girls, dress"
A2 contains "girls, hairbow"
A3. contains. "Boys, neckties"

Returns in column B
b1: clothing
b2: accessories
B3 acessorie

Your template is great and awesome and works for one word. I don't think there is a way to create a formula that will do what I ask. I have searched and searched, but yours is very, very close. Many thanks.

Can you please explain what the

"10^10,SEARCH" of your formula does?
Thanks again for all your work and help its greatly appreciated!

Hi excel1,
pl find attachment file. where I explain why use 10^10.

sambit said:

Hi excel1,
pl find attachment file. where I explain why use 10^10.

Click to expand...

Hi Sambit,
This is very helpful information. It simply amazes me as a complete excel rookie at how powerful this program really is, but most importantly the amazing things you and the other experts here, can accomplish with the numerous excel formulas. Thanks again for all your help. Im going to keep the explanation you attached for future reference and to re-read it again until I can fully grasp it.

Hi Sambit,
This is very helpful information. It simply amazes me as a complete excel rookie on how powerful this program really is, but most importantly the amazing things you and the other experts here, can accomplish with the numerous excel formulas.

If a column of cells has varying text title lengths and font size, which are constantly changing, but always will have a color, like this:

A1 The color is Blue
A2 The color is blue
A3 color is green
A4 the other color is White.

Since the titles vary in length and fonts size from cell to cell in column A, it would be hard for excel to be able to look through the titles and be able to distinguish the color word from the cell, then be able to and assign a color name for each of the specific cells A1:A4 so that it would appear like this; correct?

B1 Blue
B2 blue
B3 green
B4 White

Im assuming one, would need to have to have a formula which could look through a string of text, varying in length and font size. Then have to reference a table like yours, which would list the specific colors entered in both capital and lower case fonts, in order to accomplish this; correct?

Thanks again for all your help. Im going to keep the explanation you attached for future reference and to re-read it again until I can fully grasp it.

Found this on another site, Hope its alright to post an outside link?
its close but not sure how I would be able to use it, in order to search a single cell with a name of a product such as " this is a blue dress" in the cell A1 and the formula be able to scan through the words, then be able to zero in on one of the keywords in the title such as "blue" then assign it an value in my case a color C1 "blue".

http://en.kioskea.net/forum/affich-418197-checking-for-multiple-words-text-in-a-cell

I seem to have finally figured it out after spending several hours searching and searching the net and finding an old post listed, on the post above. After changing the original formula a bit to conform with what I need. Here is what this will do.
In cell A1, you could type or have a products title.
IE:
A1. The girls dress is blue.
Then in B1 labeled "Item_color" will place the color "blue". I am sure that others have a simpler way or formula to perform this, if so please let me know? I would be interested to know.

code usedView attachment assign_color_test.xlsx

file example attached. Thanks again Sambit for the help and info you provided.