Find max value in nth column and return adjacent cell.

[Lewzerrrr]

Hey,

I need to find the max number between B2:B100 but only looking at B2, B5, B8 etc and then return the header in A2:A100.

I can find the max between nth using =MAX(IF(MOD(COLUMN(B2:M2)-4,3),,B2:M2)) CSE but can't seem to work an index into that.

Thanks,

 

[Marcelo Branco]

Not clear.
Do you want the max of values in column B (B2, B5, B8) or the max of the values in row 2 (B2, E2, H2)?
Also i don't understand what you mean by ."..return the header in A2:A100"
A data sample along with expected results would be helpful.

M.

 

[Lewzerrrr]

No prob, see below.

90 is the max value from every 3rd column so I need to return Week4

[TABLE="width: 832"]
<colgroup><col width="64" span="13" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl64, width: 64"][/TD]
[TD="class: xl63, width: 64"]Week1[/TD]
[TD="class: xl63, width: 64"]Week2[/TD]
[TD="class: xl63, width: 64"]Week3[/TD]
[TD="class: xl63, width: 64"]Week4[/TD]
[TD="class: xl63, width: 64"]Week5[/TD]
[TD="class: xl63, width: 64"]Week6[/TD]
[TD="class: xl63, width: 64"]Week7[/TD]
[TD="class: xl63, width: 64"]Week8[/TD]
[TD="class: xl63, width: 64"]Week9[/TD]
[TD="class: xl63, width: 64"]Week10[/TD]
[TD="class: xl63, width: 64"]Week11[/TD]
[TD="class: xl63, width: 64"]Week12[/TD]
[/TR]
[TR]
[TD="class: xl64"][/TD]
[TD="class: xl63"]50[/TD]
[TD="class: xl63"]57[/TD]
[TD="class: xl63"]60[/TD]
[TD="class: xl63"]90[/TD]
[TD="class: xl63"]95[/TD]
[TD="class: xl63"]137[/TD]
[TD="class: xl63"]60[/TD]
[TD="class: xl63"]44[/TD]
[TD="class: xl63"]26[/TD]
[TD="class: xl63"]49[/TD]
[TD="class: xl63"]51[/TD]
[TD="class: xl63"]38[/TD]
[/TR]
[TR]
[TD="class: xl64"][/TD]
[TD="class: xl63"]1[/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"]1[/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"]1[/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"]1[/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[/TR]
[TR]
[TD="class: xl64"]Result =[/TD]
[TD="class: xl63"]Week 4[/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"]MAX[/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[/TR]
</tbody>[/TABLE]

 

[Marcelo Branco]

Try this array formula - assumes headers in B1:M1
=INDEX(B1:M1,MATCH(MAX(IF(MOD(COLUMN(B1:M1)-COLUMN(B1)+1,4)=0,B2:M2)),IF(MOD(COLUMN(B1:M1)-COLUMN(B1)+1,4)=0,B2:M2),0))
Ctrl+Shift+Enter

M.

 

[Lewzerrrr]

Try this array formula - assumes headers in B1:M1
=INDEX(B1:M1,MATCH(MAX(IF(MOD(COLUMN(B1:M1)-COLUMN(B1)+1,4)=0,B2:M2)),IF(MOD(COLUMN(B1:M1)-COLUMN(B1)+1,4)=0,B2:M2),0))
Ctrl+Shift+Enter

M.

Thanks Marcelo, just realised how stupid I made my first post look lol!

Just had to change the divisor to 3 and =0 to =1.

Brill