mercredi 15 mai 2019

How to INDEX MATCH based on a MAX value and returning a cell next to the MAX

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