I've been trying to solve this for a good few hours now, but with no result...
I've got excel sheets of cosmetics product recipes.
Each recipe contains dozens of ingredients, each given by its trade name.
I've got a long list translating each trade name to its 'scientific' name.
I need to translate each trade name into its scientific counterpart. This part is very easy to do with a vlookup.
But some trade names are recipes in themselves, containing multiple ingredients in the table of scientific names. So it's not a 1:1 relationship, sometimes (not always) it's 1:many.
Cosmetic A sheet
IngredientA_trade_name IngredientB_trade_name IngredientC_trade_name
Trade to Scientific names list master file:
IngredientA_trade_name Science1 IngredientB_trade_name Science2 IngredientB_trade_name Science3 IngredientB_trade_name Science4 IngredientC_trade_name Science5 IngredientC_trade_name Science6 ....etc, for lots and lots of ingredients.
Now, I've found ways to return multiple values, but none of these does the job automatically:
I must put the Ingredient name in a cell, then place the formula to the cell next to it and drag it down a few places so it autofills for all possible matches. Some other solutions put the returning values in a single cell (Science2,Science3,Science4) which is a bit better but doesn't work for the rest of my workflow...
Is there a way to go through the list of trade names and insert rows as necessary containing all the scientific names?