Partial match lookup

[iyeung26]

Hi all - first post, would appreciate some help. I have an extract from SharePoint on Excel and I want to do a lookup against my Excel mapping table.

The table I have in the spreadsheet is the "List from SharePoint" where I have a string with lots of # and ;s, I want to use that string and lookup against a table and count how many vegetables/fruit there are.

I would like the outcome of the lookup to be the numbers in orange below, is this feasible? I prefer to use the mapping table as my list of vegetables & fruit are quite long Thanks.

[TABLE="width: 509"]
<tbody>[TR]
[TD]List from SharePoint[/TD]
[TD]Lookup output: Fruit[/TD]
[TD]Lookup output: Vegetable[/TD]
[/TR]
[TR]
[TD];#Apple;#Pear;#Carrot;#[/TD]
[TD]2[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD];#Apple;#Pear;#Tomatoes;#Carrot;#[/TD]
[TD]2[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Mapping table[/TD]
[TD]Type[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Apple[/TD]
[TD]Fruit[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Pear[/TD]
[TD]Fruit[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Carrot[/TD]
[TD]Vegetable[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Tomatoes[/TD]
[TD]Vegetable[/TD]
[TD][/TD]
[/TR]
</tbody><colgroup><col><col span="2"></colgroup>[/TABLE]

 

[Aladin Akyurek]

Ditto..