IF/MAX/MATCH formula required

[colinheslop1984]

	A	B	C	D	E	F	G	H
1	week 1	week 1	week 2	week 2	week 3	week 3
2	W	R	W	R	W	R	WB	RB
3	25	10	30	8	30	12	30	FIND THIS NUMBER

I have a table of data that looks like above.
I have used MAX function to find the highest occurring number in cells labelled with "W".
In cell H3, I want to find the highest occurring number in columns titled "R", where "W" value = G3

I have used =IF(A3=G3,B3,IF(C3=G3,D5,IF(E3=G3,F3,)))

But where a "W" number repeats in the range, it just returns the first number in the table that matches the criteria of the formula (D3), rather than the highest one, which would be F3 in this case.

Can someone help write a formula that would fix this?

Thank you

 

[StephenCrump]

It would be good to know what version of Excel you're running? You can put this into your signature.

This should work in any version:

H3: =MAX(IF((A3:E3=G3)*(A2:E2="W"),B3:F3)) control-shift-enter unless you have Excel 365.

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (rather than a signature) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[colinheslop1984]

It would be good to know what version of Excel you're running? You can put this into your signature.

This should work in any version:

H3: =MAX(IF((A3:E3=G3)*(A2:E2="W"),B3:F3)) control-shift-enter unless you have Excel 365.

Thanks that worked perfectly!

 

[colinheslop1984]

What version of Excel are you using?

I suggest that you update your Account details (rather than a signature) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

I will do that, thanks for the advice