How to Lookup Values separated by commas ?

[navinrb]

Suppose i have lookup values in sheet 1as
Apple,orange,banana
Carrot, watermelon
Apricot, grapes

I want to lookup above fruits in following table in sheet 2

Fruits. Qty. . Price Apple. Banana. Orange. Watermelon. Grapes Apricot Carrot.

 

[navinrb]

Like this (for up to 5 items)?

navinrb.xlsm
	A	B
1	Apple	1
2	Banana	1
3	Orange	1
4	Watermelon	2
5	Grapes	3
6	Apricot	3
7	Carrot	2
Sheet2

navinrb.xlsm
	A	B
1	Apple,orange,banana	3
2	Carrot,watermelon	4
3	Apricot,grapes,orange	7
Sheet1

Cell Formulas
Range	Formula
B1:B3	B1	=SUMPRODUCT(IFERROR(VLOOKUP(TRIM(MID(SUBSTITUTE(","&A1,",",REPT(" ",100)),{1,2,3,4,5}*100,100)),Sheet2!A$1:B$7,2,0),0))

Does this work only for 5 items ? Anyway thank you so much .?

 

[Peter_SSs]

Does this work only for 5 items ?

You can increase the number somewhat by increasing this array

=SUMPRODUCT(IFERROR(VLOOKUP(TRIM(MID(SUBSTITUTE(","&A1,",",REPT(" ",100)),{1,2,3,4,5}*100,100)),Sheet2!A$1:B$7,2,0),0))

 

[navinrb]

You can increase the number somewhat by increasing this array

Ok Thanks a lot you for your help .

 

[Peter_SSs]

Ok Thanks a lot you for your help .

You're welcome.