match max if

[frsm]

hello all

i want to correct this formula to match my need , i want to find the value in range (b4:b405)which match the maximum value in d4:d405 when the value in c4:c405 = I4

my formula is =MAX(IF($C$4:$C$405=$I4;D$4:D$405)) and i confirm with ctrl+shift +enter

thank you

 

[Agincourt2]

Yea, I was just thinking to myself "wait that wont work if you use text values" .. disregard my last post then and use Mackers formula

 

[XOR LX]

Not sure if this is the problem, but when you match a MAX value based on a criteria, you need to use the IF() in both the first and second argument of the match.

Rep added, if for no other reason then it's simply astonishing how many people make the mistake of not realising this.

I think that approximately 3 out of every 4 solutions I see posted to a MAX with criteria question are erroneous due to this omission.

I actually prefer the non-array alternative which I illustrate here:

Return Entry Corresponding to Maximum Value Based on Conditions « EXCELXOR

which also contains an example illustrating why the conditional statements are also required within MATCH's lookup_array, as you rightly point out.

However, it should be noted that this alternative cannot be so readily amended to work for the minimum.

Regards

 

[Mackers]

Rep added, if for no other reason then it's simply astonishing how many people make the mistake of not realising this.

I think that approximately 3 out of every 4 solutions I see posted to a MAX with criteria question are erroneous due to this omission.

I actually prefer the non-array alternative which I illustrate here:

Return Entry Corresponding to Maximum Value Based on Conditions « EXCELXOR

which also contains an example illustrating why the conditional statements are also required within MATCH's lookup_array, as you rightly point out.

However, it should be noted that this alternative cannot be so readily amended to work for the minimum.

Regards

Thanks - I can't take all the credit as your (great) blog is the reason I knew of this in the first place, and I was just trying to spread the knowledge as the error is quite insidious. I've checked other peoples' workings before and it's clear that because the first rows generally return the correct results they did not pick up the errors.

Regarding the alternative construction: as other people check the workings on my spreadsheets I tend to shy away from using the lookup/frequency combination as it is very difficult to understand at first glance (especially as some people don't know what frequency does), whereas most people are fairly familiar with index and match.

All the best

Mackers

 

[XOR LX]

Thanks - I can't take all the credit as your (great) blog is the reason I knew of this in the first place

Very kind of you to say so!

I've checked other peoples' workings before and it's clear that because the first rows generally return the correct results they did not pick up the errors.

Yes - I think that's half the issue. The probability of the incorrect set-up giving a correct return by pure fortune is often quite high, and so it goes unnoticed.

Regarding the alternative construction: as other people check the workings on my spreadsheets I tend to shy away from using the lookup/frequency combination as it is very difficult to understand at first glance (especially as some people don't know what frequency does), whereas most people are fairly familiar with index and match.

Agreed. It is certainly less comprehensible, so I completely understand your sentiments in that respect.

Cheers