Lookup the largest number in a range and return a number of other factors

Afternoon all,

I've been tearing my hair out about this all afternoon and I'm hoping you can help!

I have a workbook with nice, neat tables in Sheet 1. Sheet 2 has the supporting data that feeds the tables in Sheet 1. It has product names in column A, while Row 1 has a number of repeating headings (similar to the below). I would like to get Excel to reference the product name in sheet 1 ([reference cell]), find that name in sheet 2, and then pick out the 5 suppliers with the largest inventory, from that I then want it to show me their inventory, prices etc...

Is there a formula that will achieve this? I have tried VLOOKUPs and INDEXes with LARGE, and OFFSET but to no avail.

I have included an example in the post below.

Many Thanks,
James

Try attachment.