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.

 

[mumps]

What do you want to do when you find each fruit? It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach a screenshot (not a picture) of your sheet. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).

 

[navinrb]

What do you want to do when you find each fruit? It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach a screenshot (not a picture) of your sheet. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).

I think my query is clear ..i just wann lookup comma separated items in vertically arranged item list to return price.

 

[mumps]

Please note that it is not always obvious on what you may want to do so I will give you a general idea. You would have to split each group in Sheet1 into an array and then loop through each array item to find it on Sheet2 and return the corresponding price. This appears to be fairly straight forward. If you want me to suggest a macro then please review my request in Post #2.

 

[alansidman]

This could also be done with a Power Query Merge of the two tables/ranges, however, if you would like an example of how to do it, please respond to Mumps request in Post #2. It is forum courtesy to provide as requested and not expect someone to recreate your data to give you a workable solution.

 

[Peter_SSs]

Is this what you are trying to achieve?

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

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

Cell Formulas
Range	Formula
B1:B7	B1	=VLOOKUP("* "&A1&" *"," "&SUBSTITUTE(Sheet1!A$1:B$3,","," ")&" ",2,0)

 

[navinrb]

VLOOKUP("* "&A1&" *"," "&SUBSTITUTE(Sheet1!A$1:B$3,","," ")&" ",2,0

Is this what you are trying to achieve?

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

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

Cell Formulas
Range	Formula
B1:B7	B1	=VLOOKUP("* "&A1&" *"," "&SUBSTITUTE(Sheet1!A$1:B$3,","," ")&" ",2,0)

Thank you for your time but unfortunately what i want is just reverse of your solution . Means i want sheet 2 pricde to be filled in sheet 1.

 

[Peter_SSs]

How would that look since the Sheet1 cells can contain multiple items that could be all different prices?

I think my query is clear

It seems this wasn't the case as at least 3 of us have looked without it being clear to us.

So could you actually provide a small set of sample data and expected results with XL2BB as requested earlier.

 

[navinrb]

How would that look since the Sheet1 cells can contain multiple items that could be all different prices?

It seems this wasn't the case as at least 3 of us have looked without it being clear to us.

So could you actually provide a small set of sample data and expected results with XL2BB as requested earlier.

Please have a look at screenshot.

 

[Peter_SSs]

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))