Match If with MAX, Non-Number Matches, Return other column values

[Docendo]

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!