Catergoize text in Excel sheet

[helpwantedd]

I am trying to categorize a bunch of items in a workbook. At the moment I have several search terms that I want to use. But don't know how to use excel to do that. Here is an example of what I am trying to do. Say I have a bunch of cells that have the word Red and other colors with some number after them.

A B
Red1
Red2
Blue8
Red7
Green6

Because I have a hundred of these colors I don't want to use a really long search or find. So I created a new sheet called search terms which has search terms and categories.

A B
*Red* 1
*Blue* 2
*Green* 3

I want to then create a formula in my main sheet that will search the terms and put the value I assign. So the sheets results look like this.

A B
Red1 1
Red2 1
Blue8 2
Red7 1
Green6 3

I have tried using VLOOKUP. However, it gave me incorrect results. Do you guys have any suggestion of how to help?

[DGagnon]

Is the number after the text always single digit?

[dilipandey]

Hi Helpwantedd,

Welcome to the forum.

See the attached file where I have transferred your example scenario and found the solution using offset - match formula combination. Thanks.

Regards,
DILIPandey

<click on below 'star' if this helps>

[nmhung49]

Hi Helpwantedd,

Welcome to the forum.

See the attached file where I have transferred your example scenario and found the solution using offset - match formula combination. Thanks.

Regards,
DILIPandey

I thinks with yourdata, we can lookup formula, but I need helpwantedd's data, can you post some data in worksheet

In cell C1 anh copy down
=LOOKUP(A1,$G$2:$G$4,$H$2:$H$4)

[dilipandey]

Hi nmhung,

Yes.. you are right, Lookup formula is also working fine but as you said, it would be better to have helpwantedd's data.

@ helpwantedd, could you please upload a sample workbook. Click on "Go Advanced" while replying and then look for paper clip icon to attach the workbook. Thanks.

Regards,
DILIPandey

<click on below 'star' if this helps>