Lookup and return data after using ref table

Hi All,

I have a set of raw data that I need to return in a certain way to assist analysis. It is prices of products with similar names that I need to put on one line if this is possible

It is pretty complicated so I have attached an example sheet!

In the data table I have 8 lines of products with prices, they are infact just 2 products but called slightly different by suppliers. (I am Supplier A)

In the lookup table I have manually put all similar products together in one line (It would be inmpossible to do automatically as product names can be totally different)

What I want to do is return the prices of all suppliers as per what Supplier A calls the product as in the desired results table

Is this possible??

Any help would be greatly appreciated