If Index and Match

[americanpie3]

I tried this and it won't work

=if(INDEX(SeasonSummary1!D2:D68,MATCH(A2,SeasonSummary1!A1:A68>e2)))

Basically I want to compare which score ma man Jay has highest between the 2 SeaonSummary worksheets.

With that his name could appear anywhere on either worksheet SeasonSummary1 or SeasonSummary2.

I guess you can say that I'm trying to make my sheet as dynamic as possible.

Thanks

 

[Robert Mika]

Thwre is may way if looking at this.
I hope this is what you are looking for:
If the name is in column A on both sheeets and the score is in column D
Try this:

=MAX(SUMIF('Season Summary1'!$A$2:$A$68,E2,'Season Summary1'!$D$2:$D$68),SUMIF('Season Summary 2'!$A$2:$A$68,E2,'Season Summary 2'!$D$2:$D$68))
Where E2 is the name of your man

 

[americanpie3]

Here's the SeasonSummary2 worksheet

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: center;;">Team 1</td><td style="text-align: center;;">Season Avg</td><td style="text-align: center;;">Avg Last week</td><td style="text-align: center;;">High Score Season</td><td style="text-align: center;;">High Score Last Week</td><td style="text-align: center;;">Finishes Season</td><td style="text-align: center;;">Finishes Last Week</td><td style="text-align: center;;">Highest Finish Season</td><td style="text-align: center;;">Highest Finish Last Week</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: center;;">Jason </td><td style="text-align: center;;">47.81</td><td style="text-align: center;;">47.50</td><td style="text-align: center;;">140</td><td style="text-align: center;;">100</td><td style="text-align: center;;">3</td><td style="text-align: center;;">1</td><td style="text-align: center;;">99</td><td style="text-align: center;;">16</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: center;;">Johnny</td><td style="text-align: center;;">43.31</td><td style="text-align: center;;">41.56</td><td style="text-align: center;;">124</td><td style="text-align: center;;">92</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: center;;">Dino </td><td style="text-align: center;;">37.67</td><td style="text-align: center;;">36.44</td><td style="text-align: center;;">85</td><td style="text-align: center;;">85</td><td style="text-align: center;;">4</td><td style="text-align: center;;">1</td><td style="text-align: center;;">40</td><td style="text-align: center;;">40</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet1</p><br /><br />

So as you can see SeasonSummary1 he scored 140.

Hope this helps

 

[Robert Mika]

Here's the SeasonSummary2 worksheet

Excel 2010<table rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB" cellpadding="2.5px"><colgroup><col style="background-color: #DAE7F5" width="25px"><col><col><col><col><col><col><col><col><col></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th>
</th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th></tr></thead><tbody><tr><td style="color: #161120;text-align: center;">1</td><td style="text-align: center;;">Team 1</td><td style="text-align: center;;">Season Avg</td><td style="text-align: center;;">Avg Last week</td><td style="text-align: center;;">High Score Season</td><td style="text-align: center;;">High Score Last Week</td><td style="text-align: center;;">Finishes Season</td><td style="text-align: center;;">Finishes Last Week</td><td style="text-align: center;;">Highest Finish Season</td><td style="text-align: center;;">Highest Finish Last Week</td></tr><tr><td style="color: #161120;text-align: center;">2</td><td style="text-align: center;;">Jason </td><td style="text-align: center;;">47.81</td><td style="text-align: center;;">47.50</td><td style="text-align: center;;">140</td><td style="text-align: center;;">100</td><td style="text-align: center;;">3</td><td style="text-align: center;;">1</td><td style="text-align: center;;">99</td><td style="text-align: center;;">16</td></tr><tr><td style="color: #161120;text-align: center;">3</td><td style="text-align: center;;">Johnny</td><td style="text-align: center;;">43.31</td><td style="text-align: center;;">41.56</td><td style="text-align: center;;">124</td><td style="text-align: center;;">92</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td></tr><tr><td style="color: #161120;text-align: center;">4</td><td style="text-align: center;;">Dino </td><td style="text-align: center;;">37.67</td><td style="text-align: center;;">36.44</td><td style="text-align: center;;">85</td><td style="text-align: center;;">85</td><td style="text-align: center;;">4</td><td style="text-align: center;;">1</td><td style="text-align: center;;">40</td><td style="text-align: center;;">40</td></tr></tbody></table>

Sheet1​

So as you can see SeasonSummary1 he scored 140.

Hope this helps

Is this Season1 or 2?

The formula shoudl still work - just change the ranges.