on sheet 1 I have a list of suppliers in column A, products in column b, volume of product in column c, price in column d, and costs per various units in columns e and f.

On the following sheets, I am trying to cost out various things and am trying to refer back to the price list from sheet 1. So on sheet 2, column a has the product, column b has the volume, and in column d is where i am trying to write formula to search for the product from column a on sheet 1 and return the one with the lowest price (most products have multiple entries from multiple suppliers). Ideally, column e would have a formula to reflect the supplier of that best price. I have tried to index match, and incorporate a min function but I can't seem to accomplish what I am trying to do.

Thanks in advance!

Andy