Thanks for your help. I have read through all of the various Match If posts and still having trouble.
The goal is to find the max value within a column (spend on a product), restricted by another parameter (industry), and then subsequently use those restrictions to return a value in another column (Total Spend on all products). I can easily find the max by industry, but then have trouble incorporating those restrictions in finding the other value.
Unfortunately, I see that I do not have permission to post attachments. Does anyone know how to change this so I can post an example?
The data is set up like this:
Customer (column A), Industry (column B), ProductA (column C), ProductB (column D), Total Spend (column F)
Here are the calculations and issue:
For Industry named "E":
Max spend in ProductA calculated by: =MAX(IF($B$2:$B$12="E",$C$2:$C$12)) (no problems here)
Total Spend associated with Customer in industry E spending the MAX value found above (referenced in formula as $C$16) (this is issue):
=IF(AND($B$2:$B$12="E",$C$2:$C$12=$C$16),INDEX($F$2:$F$12,MATCH($C$16,$C$2:$C$12,0)))
The formula frequently is returning FALSE because it encounters a value matching the MAX value calculated for another industry before finding the MAX value calculated for the correct industry.
Many, many thanks for your help!
The goal is to find the max value within a column (spend on a product), restricted by another parameter (industry), and then subsequently use those restrictions to return a value in another column (Total Spend on all products). I can easily find the max by industry, but then have trouble incorporating those restrictions in finding the other value.
Unfortunately, I see that I do not have permission to post attachments. Does anyone know how to change this so I can post an example?
The data is set up like this:
Customer (column A), Industry (column B), ProductA (column C), ProductB (column D), Total Spend (column F)
Here are the calculations and issue:
For Industry named "E":
Max spend in ProductA calculated by: =MAX(IF($B$2:$B$12="E",$C$2:$C$12)) (no problems here)
Total Spend associated with Customer in industry E spending the MAX value found above (referenced in formula as $C$16) (this is issue):
=IF(AND($B$2:$B$12="E",$C$2:$C$12=$C$16),INDEX($F$2:$F$12,MATCH($C$16,$C$2:$C$12,0)))
The formula frequently is returning FALSE because it encounters a value matching the MAX value calculated for another industry before finding the MAX value calculated for the correct industry.
Many, many thanks for your help!