2 Dimensional Lookup to find max value and return column header

[kangelosanto]

So I've got this data:

	A	B	C	D	E
1		AA	AB	AC	AD
2	Name A	1	3	1	0
3	Name B	5	1	0	0
4	Name C	2	0	4	8
5	Name D	1	1	0	0

<tbody>
</tbody>

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

	A	B
1	Name A	AB
2	Name B	AA
3	Name C	AC
4	Name D	AA -or- AB

<tbody>
</tbody>

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]

Row\Col	A​	B​	C​	D​	E​	F​	G​	H​	I​	J​
1​		AA	AB	AC	AD		max headers
2​	Name A	1	3	1	0		AB
3​	Name B	5	1	0	0		AA
4​	Name C	2	0	4	8		AD
5​	Name D	1	1	0	0		AA	AB

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))),"")