Excel 2007 : Problem using MAX and INDEX in one formula

[mamady]

Dears,

I want to return the maximum value in a column if it matches two categories (Carrier and Type).

So far, I did successfully to use array formula (& CSE) to get the corresponding value; however, i don’t know where to fit the MAX function.

Attached is a sample of what I want to do

I would appreciate if anyone can please take a look and help me

Thank you in advance

[JBeaucaire]

1) Change F1 to read "Premium", take out the other words
2) Change G1 to read LCR
3) Put this array formula in F2: =MAX($B$2:$B$13*(($A$2:$A$13=$E2)*($C$2:$C$13=F$1)))
...confirmed with Ctrl-Shift-Enter to activate the array
4) Copy that cell down and across the table

[mamady]

1) Change F1 to read "Premium", take out the other words
2) Change G1 to read LCR
3) Put this array formula in F2: =MAX($B$2:$B$13*(($A$2:$A$13=$E2)*($C$2:$C$13=F$1)))
...confirmed with Ctrl-Shift-Enter to activate the array
4) Copy that cell down and across the table

Thank ypu very much dear,
it works

I appreicate it

[JBeaucaire]

Thanks. BTW, there's no need to quote my posts into yours unless we're discussing something specific further from my post. Just use the QUICK REPLY button instead of the QUOTE button. Or just use the Message box below.

If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.

[mamady]

Done

I have a quick question, if I may

What does " * " do in your formula?, as I am tryining to understand the logic used.

Thanks again

[JBeaucaire]

The asterisk is the multiplication operator. If you really want to see how that formula works, click the cell, then use the Evaluate Formula icon on the Formula Auditing toolbar. Step through the formula one calc at a time.

The formula creates two arrays. The first array is all the values in in B2:B13. The second array is a series of TRUE/FALSE answers to the "tests" we did on the values in A2:A13 and C2:C13, which are multiplied together to create an array of 0s/1s.

Then the first array is multiplied into the second array, all the values multiplied by 0 disappear from the final resulting array, the ones multiplied by 1 remain, then that array is used for the final MAX() test.

[mamady]

Great

I got it, as you suggested; I just followed the steps with (Evaluate Formula) feature. You are genius.

Thanks again dear