Excel partial match from unequal columns and assign value to new column.

[xjabbott]

I want to assign a category to a table so that I can eventually create a pivot table.
I need to do a partial match to get my category values.
"num" i want to match to "number 111" and "numeric 321" and then assign both of those as category1.
"form" i want to match to "Form 1" and "formula" as category2 and so on.


[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Group[/TD]
[TD]Assigned catorgories[/TD]
[TD][/TD]
[TD]Matching critera[/TD]
[TD]Categories[/TD]
[/TR]
[TR]
[TD]Number 111
[/TD]
[TD][/TD]
[TD][/TD]
[TD]num[/TD]
[TD]category1[/TD]
[/TR]
[TR]
[TD]Numeric 321[/TD]
[TD][/TD]
[TD][/TD]
[TD]form[/TD]
[TD]category2[/TD]
[/TR]
[TR]
[TD]Form 1[/TD]
[TD][/TD]
[TD][/TD]
[TD]data[/TD]
[TD]category3
[/TD]
[/TR]
[TR]
[TD]Uncategorized[/TD]
[TD][/TD]
[TD][/TD]
[TD]misc[/TD]
[TD]category4[/TD]
[/TR]
[TR]
[TD]test data[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]prod data[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]formula[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Bo_Ry]

Please try
B2
=LOOKUP(0,-SEARCH($D$2:$D$5,A2),$E$2:$E$5)
or
Uncategorized => category4
=IFERROR(LOOKUP(0,-SEARCH($D$2:$D$5,A2),$E$2:$E$5),$E$5)

 

[xjabbott]

Thanks Bo_Ry... that worked... I was so sunk into vlookup or index and match that I didn't consider lookup.