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

 

[Jonmo1]

How about

=INDEX(B4:B405,MATCH(MAX(IF($C$4:$C$405=$I4;D$4:D$405)),D4:D405,0))

Still confirmed with CSE

 

[frsm]

sorry, it doesn't give the exact result

i have in b4:b405 names like
mike
johne
philpe
.
..
.
in c4:c405 I've the one of the numbers from 1-9 (class number)
1
3
8
6
.
.
.
in d4:d405 i have different values

120
60
43
25
.
.
.
.
i need to find the name wihvh match the max value of d4:d405 for each class 1;2;3;

help please

thank you

 

[Jonmo1]

it works for me...Did you re enter the formula with CTRL + SHIFT + ENTER ??

when entered correctly the formula will look like

{=INDEX(B4:B405,MATCH(MAX(IF($C$4:$C$405=$I4;D$4:D$405)),D4:D405,0))}


Works fine for me, see below.<SCRIPT language=JavaScript src="<A href="http://www.interq.or.jp/sun/puremis/colo/popup.js"></SCRIPT>

Personal.xls
	B	C	D	E	F	G	H	I
4	mike	1	80					8
5	john	3	60
6	phillip	8	43					phillip
7	fred	3	25
8	alice	8	32
9	barney	1	120
Sheet1

 

[Jonmo1]

I think you might be trying to fill it down to check each class number in column I ?

then You need to add the absolute references...

=INDEX($B$4:$B$405,MATCH(MAX(IF($C$4:$C$405=I4,$D$4:$D$405)),$D$4:$D$405,0))

<SCRIPT language=JavaScript src="<A href="http://www.interq.or.jp/sun/puremis/colo/popup.js"></SCRIPT>

Personal.xls
	B	C	D	E	F	G	H	I	J
4	mike	1	80					1	barney
5	john	3	60					3	john
6	phillip	8	43					8	phillip
7	fred	3	25
8	alice	8	32
9	barney	1	120
Sheet1

 

[frsm]

ok thank you ,
it works fine for the fist vakue the it gives errors with the other
plz put the srial numbers from 1-9 iin the cells I4- I12 the put please the formula in the range h4 to H12 , i did that and found some errors inthe results
thank you

 

[Jonmo1]

I've added that...please see last post...

 

[frsm]

i added add the absolute references but i think the problem is there are equal values in d4:d405 so it match the first name that catch the specific value without consideration to its class

 

[Agincourt2]

I had a very similar problem, after reading this thread I tinkered with the formulas. The issue in the case above is that if there are duplicate values in d4:d405 then only the first occurrence is displayed. My own issue was finding the last product ID used by any account out 65,652 total accounts. Where I needed to find the max date per account ID, then display the corresponding product ID. For my example, using the above formula I found the max date by account using the max if formula, but the match formula would only look at the "date" itself when used in the index and not the date and account ID.

I resolved this with using a double MAX IF (nested MAX IF?) without the index match formula. Using the case above the formula would be:

MAX(IF($C$4:$C$405=I4,IF($D$4:$D$405=MAX(IF($C$4:$C$405=I4,$D$4:$D$405)),$B$4:$B$405))) with ctrl+shift +enter

 

[Mackers]

Hi all

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. As such your formula earlier should read as follows:

=INDEX($B$4:$B$405,MATCH(MAX(IF($C$4:$C$405=I4,$D$4:$D$405)),IF($C$4:$C$405=I4,$D$4:$D$405),0))

Imagine a table sorted alphabetically containing first names and then points, with each person having several scores. If you find the maximum score of Zack as 20, you then match on the scores. If Abigail also scored a 20 and you don't remove her result using the IF(Name=Zack, Scores), you will match to her row instead of Zack's.

Hope that helps

Mackers