Help with finding & returning text within a cell from a list of multiple options

[odyssey112]

Hi,

I need help with a formula to return some data from a spreadsheet that I've been working please.

I have a list of data like the below and am trying to extract ingredients based on a separate list that I've created. So for example, my list will include ingredients like calcium or vitamin E or vitamin c. With my formula I'm trying to look up the data below and if it contains an ingredient from my list return that ingredient.

I've used the below 2 which return the same values and that's fine but I have instances where there might be a product with multiple ingredients from my list. I'd like excel to return just either all of the them or just the first one from the actual description rather than the first one from my list of ingredients. Hopefully that makes sense - does anyone have any ideas?

Thanks

=INDEX(lookup!$A$1:$A$23,MATCH(TRUE,ISNUMBER(SEARCH(lookup!$A$1:$A$23,A2)),0))
=IFERROR(INDEX(lookup!$A$2:$A$18,MATCH(1,COUNTIF(A2,"*"&lookup!$A$2:$A$18&"*"),0)),"")

Club Vits Vitamin E 200iu - 1000 Capsules
Quest Synergistic Calcium - 45 Tablets
Solgar Vitamin C 500 mg Vegetable Capsules - Pack of 100
Higher Nature True Food C 90 Tablets

[AliGW]

Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however the thread title does not really convey what your request is about.

Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

(Note: this change is not optional. No help to be offered until this moderation request has been fulfilled. I would do it for you, but I am not 100% clear what you are trying to achieve.)