Match, Index, Lookup?

[Chinnick]

Sorry for the heading, not really sure how to word what i am asking for here.

Normally i would just write a macro for what i am trying to do but the people this is sent out to have trouble enabling macros.

What i am trying to do is to change the column i index the values from based on where the proper column is.

I have 1 sheet that imports all the golfer scores
Another sheet that matches the golfers name with the total score they have.
The problem is that the site i use to import the stats seems to like to change the total score column on occassion

I can do a match to find the row the Name value is on
<code>=MATCH("Name",'Tournament Scores'!B:B,0)</code>
in that case i get a value of 88
I can also do
<code>=MATCH("Total",'Tournament Scores'!88:88,0)</code>
to get a value of 9 to tell me what column the totals are in

how can i change the 88's to be my code that finds the 88?

I am using Excel 2003 for this application as well

thanks in advance

 

[CharlesChuckieCharles]

why not try looking at SUMIF or SUMIFS?

 

[CWatts]

If you dont use a lot of these functions, you can use this:

=MATCH("Total",INDIRECT("'Tournament Scores'!"&MATCH("Name",'Tournament Scores'!B:B,0)&":"&MATCH("Name",'Tournament Scores'!B:B,0)),0)

 

[Chinnick]

If you dont use a lot of these functions, you can use this:

=MATCH("Total",INDIRECT("'Tournament Scores'!"&MATCH("Name",'Tournament Scores'!B:B,0)&":"&MATCH("Name",'Tournament Scores'!B:B,0)),0)

Thanks,
I had never used Indirect before, i had to rewrite the formula due to the length but it needed to be cleaned up anyway!