I've been trying to solve this for a good few hours now, but with no result...
My setup:
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.
For example:
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?