Help with Formulas!

[zarealshook]

Hi,

Hope all is good at your end. I need assistance with retrieving the highest monthly demand for products in my Excel sheet.

Setup

• Analysis Sheet:
  
  • Column G (G113 to G122): Contains manually entered rank numbers (1 to 10).
  • Cell H113: Should display the product name for the highest demand.
  • Cell I113: Should display the highest demand quantity.
  • Cell J113: Should display the month with the highest demand.
• BOM & MRP Details Sheet:
  
  • Column D (D5 to D121): Product names.
  • Columns F to Q (F5 to Q121): Monthly demand data (Jan to Dec).

Problem​

I want to pull the highest monthly demand for each ranked product and display:

1. The product name.
2. The highest demand quantity.
3. The month of the highest demand.

How can I structure formulas to achieve this for each rank (1-10), automatically pulling the correct product name, demand, and month without manual checks?

Thanks in advance!

 

[zarealshook]

Hi ! Can anyone please be kind to assist with this problem?

Appreciate any early response.

Regards,