Formula to meet multiple criteria and perform calculation

Hello
I have a data given below. I want to do the following-
Define formula after Equipment-3 column such that, search which 'Product' use Equipment-1, then find highest 'weightage' among the product uses 'Equipment-1' and then pick the 'Dose' of the highest 'weightage' value product and muliply with 'batch' of the product in that row and divide by 'Dose in that row. Like in below case forrmula shall search that Product A & D uses R-50 and Product D has maximum Weighatge (64), Hence in row of product 'A' in cell next to 'Equipment-3', automatically formula shall multiply 'Dose' of D i.e. 250 with Batch of 'A' i.e. 150 and divide bby Dose of 'A' i.e. 5...This is to bbe done of each column "equipment-1, 2 and 3...so on. Please help

Product Batch Dose weightage Equipment-1 Equipment-2 Equipment-3
A 150 5 55 R-50
B 60 100 61 R250
c 500 1 54 R500
D 200 250 61 R-50
E 10 250 54 R500

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

I have attache an excel sheet for data. I have following request.

In attached excel sheet, I want to define a formula in each cell of column ‘P’ (first in ‘P3’).
I want to first search the products in (column A), which are having ‘Equipments’ populated in ‘column J’.
For all products having equipments populated in column J, I want to find single maximum ‘weightage’ value in column G.
The product which is having maximum ‘weightage’, I want to Multiply the ‘Dose’ value from ‘column C’; with B3 (Kg of first product)
and divide by C3 (Dose of first product).

In case more then one product found having maximum weightage (Column G value) , then formula shall consider the product having
‘Solubility’ (Column D) having higher value among the identified multiple products having highest weightage value.

This formula will be defined for entire column P and corresponding product values will change (Column B ‘Kg’ for multiplication
and Column C ‘Dose’ for division). Also similar search for other columns like K,L and so on shall be done and formula shall be
defined in column column Q and R….

Please suggest

What happens when weight and solubility are the same?

Weightage shall be highest, among more than one product with highest weightage, solubility with heighest value. Rrest of parameters has no impact.

Please suggest

Dear Sir,

I have attached the file again with exact requirement mentioned in text form. Hope this will clarify the requirement. Thanks for your quick response

I understand the logic but changes the value of "Weightage" ("Weighting"?) in your example ignores my question: any solution will assume there is a uniqure maximum weighting for any column.

I used helper columns V:AB

Formula

=IF(J3<>"",($G3&$D3)+0,"") to give Weighting & Solubilty

In P3

=IF(J3<>"",INDEX($C3:$C14,MATCH(MAX(V3:V14),V3:V14,0))*$B3/$C3,"")

Copy across and down

Thanks sir, its working. But is it possible to do it without using helper columns?
Many thanks for your help

Thanks alot sir. Really appreciate your help.

Better

=IF(J3<>"",INDEX($C$3:$C$16,MATCH(MAX(IF(J$3:J$16<>"",($G$3:$G$16&$D$3:$D$16)+0,"")),($G$3:$G$16&$D$3:$D$16)+0,0))*$B3/$C3,"")