I have a table with product name, quantity1, quantity2, price. If a product has a Quantity1 value >0 it have a Quantity2 value of 0 and vica versa. I want to write a formula to return the product name with the highest list price if it has a number >0 in quantity1.
An example table is:
Product Name__ Quantity1__ Quantity2__ Price
ProdA_____________5_________0______300
ProdB_____________0________10______400
ProdC_____________0_________7_____1000
ProdD_____________5_________0_____1000
I attempted an INDEX MATCH that leveraged an IF statement and a MAX statement.
=INDEX(Product Name Range,MATCH(MAX(IF(Quantity1 Range>0,Price Range)),Price Range,0))
The problem is that the INDEX MATCH will pull the first product with the same list price identified with the MAX statement, resulting in incorrect results. This formula in the example table above would pull ProdC incorrectly.
Aucun commentaire:
Enregistrer un commentaire