2 Dimensional Lookup to find max value and return column header

[kangelosanto]

So I've got this data:

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD]AA[/TD]
[TD]AB[/TD]
[TD]AC[/TD]
[TD]AD[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Name A[/TD]
[TD]1[/TD]
[TD]3[/TD]
[TD]1[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Name B[/TD]
[TD]5[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Name C[/TD]
[TD]2[/TD]
[TD]0[/TD]
[TD]4[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Name D[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
</tbody>[/TABLE]

And I can't for the life of me figure out how to achieve this:

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Name A[/TD]
[TD]AB[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Name B[/TD]
[TD]AA[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Name C[/TD]
[TD]AC[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Name D[/TD]
[TD]AA -or- AB[/TD]
[/TR]
</tbody>[/TABLE]


The Value in B4 can be either AA, AB or a CONCATENATE of the two, I'm not really worried about that part as it should only happen extremely sparingly.

I tried a combination of INDEX(MATCH(MAX etc. but nothing has worked. Help!

 

[kangelosanto]

Name C should return "AD", sorry, typo.

 

[Marcelo Branco]

Try this in Sheet2 B1

=INDEX(Sheet1!$B$1:$E$1,MATCH(MAX(INDEX(Sheet1!B:E,MATCH(A1,Sheet1!A:A,0),0)),INDEX(Sheet1!B:E,MATCH(A1,Sheet1!A:A,0),0),0))
copy down

Hope this helps

M.

 

[Aladin Akyurek]

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][td]

F​

[/td][td]

G​

[/td][td]

H​

[/td][td]

I​

[/td][td]

J​

[/td][/tr]
[tr][td]

1​

[/td][td] [/td][td] AA[/td][td] AB[/td][td] AC[/td][td] AD[/td][td][/td][td] max headers[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

2​

[/td][td] Name A[/td][td] 1[/td][td] 3[/td][td] 1[/td][td] 0[/td][td][/td][td]AB[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

3​

[/td][td] Name B[/td][td] 5[/td][td] 1[/td][td] 0[/td][td] 0[/td][td][/td][td]AA[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

4​

[/td][td] Name C[/td][td] 2[/td][td] 0[/td][td] 4[/td][td] 8[/td][td][/td][td]AD[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

5​

[/td][td] Name D[/td][td] 1[/td][td] 1[/td][td] 0[/td][td] 0[/td][td][/td][td]AA[/td][td]AB[/td][td][/td][td][/td][/tr]
[/table]

G2, control+shift+enter, not just enter, copy across, and down:

=IF(COLUMNS($G2:G2)<=COUNTIFS($B2:$E2,MAX($B2:$E2)),
    INDEX($B$1:$E$1,SMALL(IF($B2:$E2=MAX($B2:$E2),COLUMN($B$1:$E$1)-COLUMN($B$1)+1),
    COLUMNS($G2:G2))),"")