Returning the 2nd, 3rd, 4th values from a cell based on a list

[odyssey112]

Hi,

I have a list of products in column A that contain different ingredients. I'm looking for a formula that can extract all ingredients found in column A2 from a separate list that I've created. I can find the first from the list and the last but there are some product that might contain up to 4 of the ingredients from my list and I'm struggling to find a formula to get to this.

Any help would be appreciated!

Thanks
Emily

 

[Special-K99]

Post a sample spreadsheet with expected results, remove any sensitive data, create a mockup example if necessary.
You cant attach files on this forum. There are tools on this forum for adding small spreadsheet images

https://www.mrexcel.com/forum/about-board/508133-attachments.html

 

[Rick Rothstein]

I'm looking for a formula that can extract all ingredients found in column A2 from a separate list that I've created.

Does this formula do what you want where I have assumed your ingredients are delimited by a comma (shown in red below)...

=TRIM(MID(SUBSTITUTE(","&$A2,",",REPT(" ",300)),COLUMNS($B:B)*300,300))

 

[DanteAmor]

Hi,
I have a list of products in column A that contain different ingredients. I'm looking for a formula that can extract all ingredients found in column A2 from a separate list that I've created. I can find the first from the list and the last but there are some product that might contain up to 4 of the ingredients from my list and I'm struggling to find a formula to get to this.

Hi @odyssey112, welcome to the forum!

You mention that you have a list of ingredients, it can be like the following example in sd cell range: G5:G10
And in cell A2 you have information, and in cell B2 you want to put the A2 ingredients that match the ingredient list.
Maybe something like this:

<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:220.51px;" /><col style="width:140.67px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:98.85px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td><td >G</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="background-color:#ffff00; ">Product</td><td style="background-color:#ffff00; ">Ingredients</td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:74px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td >1 tablespoon olive oil.   <br />2 medium carrots, chopped. <br />1 garlic clove, minced. <br />3/4 pound salmon fillets</td><td >carrot, garlic</td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td style="background-color:#ffff00; ">Ingredients</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td >onion</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td >carrot</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td >garlic</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td >tomato</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td >pepper</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td >salt</td></tr></table>

If it is not something similar, then you should give some examples.