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.

List from SharePoint	Lookup output: Fruit	Lookup output: Vegetable
;#Apple;#Pear;#Carrot;#	2	1
;#Apple;#Pear;#Tomatoes;#Carrot;#	2	2
Mapping table	Type
Apple	Fruit
Pear	Fruit
Carrot	Vegetable
Tomatoes	Vegetable

<tbody>
</tbody><colgroup><col><col span="2"></colgroup>

 

[iyeung26]

Expert opinions needed please!

 

[Peter_SSs]

Welcome to the MrExcel board!

Firstly a newbie tip: Even if you are in a hurry, don't bump your post so quickly. At the top of the forum is a "Zero Reply Posts" and many of the experienced helpers go there first to look for questions that need answering. As soon as you replied to your own thread, it no longer appeared in that list, so you immediately tend to get less 'lookers' & potential helpers. If you haven't done so yet, I suggest that you have a good look at the Forum Rules and the Forum Use Guidelines.

Now for your problem. Does this do what you want? This is an array formula so should be entered without the {} but confirmed with Ctrl+Shift+Enter, not just Enter. If confirmed correctly, Excel will insert the {}. The formula can then be copied across and down.

Excel Workbook
	A	B	C
1	List from SharePoint	Fruit	Vegetable
2	;#Apple;#Pear;#Carrot;#	2	1
3	;#Apple;#Pear;#Tomatoes;#Carrot;#	2	2
4
5
6
7	Mapping table	Type
8	Apple	Fruit
9	Pear	Fruit
10	Carrot	Vegetable
11	Tomatoes	Vegetable
Count

 

[Aladin Akyurek]

List from SharePoint	Fruit	Vegetable
;#Apple;#Pear;#Carrot;#	2	1
;#Apple;#Pear;#Tomatoes;#Carrot;#	2	2
Mapping table	Type
Apple	Fruit
Pear	Fruit
Carrot	Vegetable
Tomatoes	Vegetable

<tbody>
</tbody>

In B2 control+shift+enter, not just enter, copy across, and down:

=SUM(IF(ISNUMBER(SEARCH($A$6:$A$9,$A2)),IF($B$6:$B$9=B$1,1)))

 

[Peter_SSs]

In B2 control+shift+enter, not just enter, copy across, and down:

=SUM(IF(ISNUMBER(SEARCH($A$6:$A$9,$A2)),IF($B$6:$B$9=B$1,1)))

Hi Aladin

Note that is not robust against fruits and vegetables that share a common word. For example, the mapping table already has the Fruit "Apple", but if it also included the Vegetable "Ground Apple" (from Peru) then your formula would give an incorrect count for fruit for the string ";#Ground Apple;#Pear;#Carrot;#"

 

[Aladin Akyurek]

Hi Aladin

Note that is not robust against fruits and vegetables that share a common word. For example, the mapping table already has the Fruit "Apple", but if it also included the Vegetable "Ground Apple" (from Peru) then your formula would give an incorrect count for fruit for the string ";#Ground Apple;#Pear;#Carrot;#"

Thanks. That's easily corrected for...

=SUM(IF(ISNUMBER(SEARCH(";#"&$A$6:$A$9&";#",SUBSTITUTE($A2," ",""))),IF($B$6:$B$9=B$1,1)))

 

[Peter_SSs]

That's easily corrected for...

=SUM(IF(ISNUMBER(SEARCH(";#"&$A$6:$A$9&";#",SUBSTITUTE($A2," ",""))),IF($B$6:$B$9=B$1,1)))

That still fails for the example I gave as the SUBSTITUTE then naturally fails to match GroundApple with Ground Apple.
I think with your approach, the following should suffice

{=SUM(IF(ISNUMBER(SEARCH("#"&$A$6:$A$9&";",$A2)),IF($B$6:$B$9=B$1,1)))}

 

[Aladin Akyurek]

That still fails for the example I gave as the SUBSTITUTE then naturally fails to match GroundApple with Ground Apple.
I think with your approach, the following should suffice

{=SUM(IF(ISNUMBER(SEARCH("#"&$A$6:$A$9&";",$A2)),IF($B$6:$B$9=B$1,1)))}

Or...

{=SUM(IF(ISNUMBER(SEARCH(";#"&SUBSTITUTE($A$6:$A$10," ","")&";#",SUBSTITUTE($A2," ",""))),IF($B$6:$B$10=B$1,1)))}

The idea is clear and simple, although never exhaustively safe for any possible case.

 

[iyeung26]

Works perfectly, thanks Peter & Aladin. Much appreciated

 

[Peter_SSs]

You are very welcome.