I have a table where Products are mapped with ingredients and another table where Ingredients are mapped with IDs like this below :
Below is what I need :
Here I want to replace the comma separated ingredients values in "Data Table" with their IDs from "Master Table".
I checked out Replace Function to accomplish this task but couldn't manage to do it somehow.
=REPLACE(C2,1,LEN(C2),..
Here C2 i.e. Ingredient column in "Data Table" doesn't have single value to replace it easily, they are available in combination for which replace statement is not the correct solution although the same can be handled programmatically but I want to do it in excel way.
It would be very grateful if someone can guide me with solution to this problem.
Thanks in advance.
This is not a direct solution nor a VBA solution but through a manual action and 2 simple formula's, this is possible.
I used a combination of Data>Text to Columns, VLOOKUP and Concatenate functions to achieve your result table.
Step 1.: Delimit your data using Text To Column.
Step 2.: Add VLOOKUP Functions for each ingredients.
Step 3. Use the Concatenate function to bring all the VLOOKUPS together.
You can find my solution using your example here in a Google sheet:
