Nested If Formula Help Needed

Hello:

I currently am in need of some help to extract the best possible price on a vendor quote. I have attached a spreadsheet.

What I am looking for is the following:

1) I have created templates (Seperate) for pricing on SKU's for Vendor A and Vendor B. I have been able to extract by Vlookup my cheapest vendor. This part I have been able to do in Column AC once I paste the SKU in column AB.

2) I am trying an "HLookup" in Columns AD and AE to pull the vendor "Material Cost" and "Vendor Cost." I am using my cheapest vendor from column AC as my lookup value in the HLookup.

When I do this for the same SKU I am receiving different costs for Material and Packaging Costs. I am thinking I need a "Nested IF" statement?

I am extracting the data in Columns AD and AE from master data in columns H,I,K, and L.

Any help is appreciated.

Kind Regards.

Your F column which you use for your row_index_number in the HLOOKUP formula is different for the 2 rows... is that what is causing your concern? If not, what are you expecting as a result and why?

Try in AD3:

=INDEX($H$3:$I$6,MATCH(AB3,$A$3:$A$6,0),MATCH(AC3,$H$2:$I$2,0))

and in AE3:

=INDEX($K$3:$L$6,MATCH(AB3,$A$3:$A$6,0),MATCH(AC3,$K$2:$L$2,0))

both copied down