Double Decker Index Match Sandwich?

[agutts6]

So someone helped me here the other day with the first step of my issue... I was trying to return a text reference from the column headings of the following data...

<table width="320" border="0" cellpadding="0" cellspacing="0"><col style="width: 48pt;" width="64" span="5"> <tbody><tr style="height: 15pt;" height="20"> <td style="height: 15pt; width: 48pt;" width="64" height="20">Name</td> <td style="width: 48pt;" width="64">2B</td> <td style="width: 48pt;" width="64">SS</td> <td style="width: 48pt;" width="64">3B</td> <td style="width: 48pt;" width="64">1B</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">Joe</td> <td align="right">23</td> <td align="right">45</td> <td align="right">0</td> <td align="right">0</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">Steve</td> <td align="right">0</td> <td align="right">0</td> <td align="right">60</td> <td align="right">0
</td> </tr> </tbody></table>
(Baseball games played by position, in case you care).

I was given the following solution for cell F2, which works beautifully: =INDEX(B$1:E$1,MATCH(MAX(B2:E2),B2:E2,0)) ... this returns "SS", just as it should.

Here's my next issue.... I have a whole page full of 'Joes' and 'Steves' in the fashion you see here. I've got to sync them with other data I have on Joe and Steve on another page.

So, if on sheet2, I've got 'Steve'... how do I modify that formula so that I can have that cell reference do the same thing as above? I was thinking I'd have to nest another index:match inside the match function of the original formula above, but can't quite wrap my mortal brain around what I'm supposed to do.

If you help me, I'll make all your wildest dreams come true. Sexually.

Thanks,

--AG

 

[T. Valko]

So someone helped me here the other day with the first step of my issue... I was trying to return a text reference from the column headings of the following data...

<TABLE cellSpacing=0 cellPadding=0 width=320 border=0><COLGROUP><COL style="WIDTH: 48pt" span=5 width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="WIDTH: 48pt; HEIGHT: 15pt" width=64 height=20>Name</TD><TD style="WIDTH: 48pt" width=64>2B</TD><TD style="WIDTH: 48pt" width=64>SS</TD><TD style="WIDTH: 48pt" width=64>3B</TD><TD style="WIDTH: 48pt" width=64>1B</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="HEIGHT: 15pt" height=20>Joe</TD><TD align=right>23</TD><TD align=right>45</TD><TD align=right>0</TD><TD align=right>0</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="HEIGHT: 15pt" height=20>Steve</TD><TD align=right>0</TD><TD align=right>0</TD><TD align=right>60</TD><TD align=right>0

</TD></TR></TBODY></TABLE>
(Baseball games played by position, in case you care).

I was given the following solution for cell F2, which works beautifully: =INDEX(B$1:E$1,MATCH(MAX(B2:E2),B2:E2,0)) ... this returns "SS", just as it should.

Here's my next issue.... I have a whole page full of 'Joes' and 'Steves' in the fashion you see here. I've got to sync them with other data I have on Joe and Steve on another page.

So, if on sheet2, I've got 'Steve'... how do I modify that formula so that I can have that cell reference do the same thing as above? I was thinking I'd have to nest another index:match inside the match function of the original formula above, but can't quite wrap my mortal brain around what I'm supposed to do.

If you help me, I'll make all your wildest dreams come true. Sexually.

Thanks,

--AG

Try this...

Book1
	A	B	C	D	E
1	Name	2B	SS	3B	1B
2	Joe	23	45	0	0
3	Steve	0	0	60	0
Sheet1

A10 = some name like Steve

Enter this formula in B10:

=INDEX(B$1:E$1,MATCH(MAX(INDEX(B2:E3,MATCH(A10,A2:A3,0),0)),INDEX(B2:E3,MATCH(A10,A2:A3,0),0),0))

 

[agutts6]

I think I love you Valko!!!

 

[T. Valko]

I think I love you Valko!!!

Thanks for the feedback!