Create new rows in a new sheet based on the selected options in a drop down of a new sheet



Source sheet has data like so:

The destination file needs to be like so:

I tried different variations of VLOOKUP but I was not able to make it work any suggestions on how to get this desired result with a formula?