Index function to find nth occurrence
 

I need help with a golf tournament spreadsheet. Golfers are in two
divisions and I can find the lowest raw score and name in each
division. Not all golfers compete each week, so there are many raw
scores of zero that need to be ignored. I use named ranges.

To determine if there is a two-way tie within the division, I have this
formula in cell B19:
=IF(MIN(IF(Division="Semi",IF(RawScores<0,RawScor es,FALSE),FALSE))=SMALL(IF(Division="Semi",IF(RawS cores<0,RawScores,FALSE),FALSE),2),"tied","no
tie for low")

The problem is finding the golfers name if there is a tie. This is the
formula I tried. It first checks to see if there is a tie by looking
at the results of the above formula, then is supposed to pull out the
name of the second person. It doesn't work, though...it just pulls out
the first name it comes to with the low score.
=IF($B$19="tied",INDEX($A$51:$H$161,MATCH(SMALL(IF (Division="Semi",IF(RawScores<0,RawScores,FALSE), FALSE),2),RawScores,0),1),"")

I read the arbitrary lookups section on Pearson's site
(http://www.cpearson.com/excel/lookups.htm), but don't know how to
modify the formulas to fit my situation. Can anyone please help?

Index function to find nth occurrence
 

Find the 2nd instance of "tied".

Screencap:

http://img293.imageshack.us/img293/1359/sampledt4.jpg

Biff

"Ken" wrote in message
oups.com...
I need help with a golf tournament spreadsheet. Golfers are in two
divisions and I can find the lowest raw score and name in each
division. Not all golfers compete each week, so there are many raw
scores of zero that need to be ignored. I use named ranges.

To determine if there is a two-way tie within the division, I have this
formula in cell B19:
=IF(MIN(IF(Division="Semi",IF(RawScores<0,RawScor es,FALSE),FALSE))=SMALL(IF(Division="Semi",IF(RawS cores<0,RawScores,FALSE),FALSE),2),"tied","no
tie for low")

The problem is finding the golfers name if there is a tie. This is the
formula I tried. It first checks to see if there is a tie by looking
at the results of the above formula, then is supposed to pull out the
name of the second person. It doesn't work, though...it just pulls out
the first name it comes to with the low score.
=IF($B$19="tied",INDEX($A$51:$H$161,MATCH(SMALL(IF (Division="Semi",IF(RawScores<0,RawScores,FALSE), FALSE),2),RawScores,0),1),"")

I read the arbitrary lookups section on Pearson's site
(http://www.cpearson.com/excel/lookups.htm), but don't know how to
modify the formulas to fit my situation. Can anyone please help?

Index function to find nth occurrence
 

Hi Ken

this looks very similar to Biff's solution, uses the INDEX function - assume
you have names in column A, RawScores in B (using CSE to enter):

{=INDEX(A1:B13,SMALL(IF(RawScores=MIN(IF(RawScores <0,RawScores,FALSE)),ROW(RawScores)-ROW(B1)+1,ROW(B13)+1),{1;2}),1)}

Should return the two tied names for lowest score, assuming there are only
two (This does have to be entered into two-cell array). If there are more,
this could be adapted by entering an n-cell array, replacing {1;2} with
{1;2;...;n}.

Hope this helps

Geoff

--
There are 10 types of people in the world - those who understand binary and
those who don't.


"Ken" wrote:

I need help with a golf tournament spreadsheet. Golfers are in two
divisions and I can find the lowest raw score and name in each
division. Not all golfers compete each week, so there are many raw
scores of zero that need to be ignored. I use named ranges.

To determine if there is a two-way tie within the division, I have this
formula in cell B19:
=IF(MIN(IF(Division="Semi",IF(RawScores<0,RawScor es,FALSE),FALSE))=SMALL(IF(Division="Semi",IF(RawS cores<0,RawScores,FALSE),FALSE),2),"tied","no
tie for low")

The problem is finding the golfers name if there is a tie. This is the
formula I tried. It first checks to see if there is a tie by looking
at the results of the above formula, then is supposed to pull out the
name of the second person. It doesn't work, though...it just pulls out
the first name it comes to with the low score.
=IF($B$19="tied",INDEX($A$51:$H$161,MATCH(SMALL(IF (Division="Semi",IF(RawScores<0,RawScores,FALSE), FALSE),2),RawScores,0),1),"")

I read the arbitrary lookups section on Pearson's site
(http://www.cpearson.com/excel/lookups.htm), but don't know how to
modify the formulas to fit my situation. Can anyone please help?

Index function to find nth occurrence
 

Thanks for the quick response Biff.

I created a separate spreadsheet that mirrored your example and entered
the array formula given. The error message returned was "you've
entered too few arguments." I made a correction to the formula and it
worked perfectly!! Revised Formula is:

=INDEX(Name,SMALL(IF(Status="tied",ROW(Name)-MIN(ROW(Name))+1,FALSE),2))

Thanks so much!!


Biff wrote:
Find the 2nd instance of "tied".

Screencap:

http://img293.imageshack.us/img293/1359/sampledt4.jpg

Biff

"Ken" wrote in message
oups.com...
I need help with a golf tournament spreadsheet. Golfers are in two
divisions and I can find the lowest raw score and name in each
division. Not all golfers compete each week, so there are many raw
scores of zero that need to be ignored. I use named ranges.

To determine if there is a two-way tie within the division, I have this
formula in cell B19:
=IF(MIN(IF(Division="Semi",IF(RawScores<0,RawScor es,FALSE),FALSE))=SMALL(IF(Division="Semi",IF(RawS cores<0,RawScores,FALSE),FALSE),2),"tied","no
tie for low")

The problem is finding the golfers name if there is a tie. This is the
formula I tried. It first checks to see if there is a tie by looking
at the results of the above formula, then is supposed to pull out the
name of the second person. It doesn't work, though...it just pulls out
the first name it comes to with the low score.
=IF($B$19="tied",INDEX($A$51:$H$161,MATCH(SMALL(IF (Division="Semi",IF(RawScores<0,RawScores,FALSE), FALSE),2),RawScores,0),1),"")

I read the arbitrary lookups section on Pearson's site
(http://www.cpearson.com/excel/lookups.htm), but don't know how to
modify the formulas to fit my situation. Can anyone please help?

Index function to find nth occurrence
 

You're welcome. Thanks for the feedback!

You really don't need the FALSE argument but having it doesn't change
anything. The formula still works the same with or without it.

Biff

"Ken" wrote in message
oups.com...
Thanks for the quick response Biff.

I created a separate spreadsheet that mirrored your example and entered
the array formula given. The error message returned was "you've
entered too few arguments." I made a correction to the formula and it
worked perfectly!! Revised Formula is:

=INDEX(Name,SMALL(IF(Status="tied",ROW(Name)-MIN(ROW(Name))+1,FALSE),2))

Thanks so much!!


Biff wrote:
Find the 2nd instance of "tied".

Screencap:

http://img293.imageshack.us/img293/1359/sampledt4.jpg

Biff

"Ken" wrote in message
oups.com...
I need help with a golf tournament spreadsheet. Golfers are in two
divisions and I can find the lowest raw score and name in each
division. Not all golfers compete each week, so there are many raw
scores of zero that need to be ignored. I use named ranges.

To determine if there is a two-way tie within the division, I have this
formula in cell B19:
=IF(MIN(IF(Division="Semi",IF(RawScores<0,RawScor es,FALSE),FALSE))=SMALL(IF(Division="Semi",IF(RawS cores<0,RawScores,FALSE),FALSE),2),"tied","no
tie for low")

The problem is finding the golfers name if there is a tie. This is the
formula I tried. It first checks to see if there is a tie by looking
at the results of the above formula, then is supposed to pull out the
name of the second person. It doesn't work, though...it just pulls out
the first name it comes to with the low score.
=IF($B$19="tied",INDEX($A$51:$H$161,MATCH(SMALL(IF (Division="Semi",IF(RawScores<0,RawScores,FALSE), FALSE),2),RawScores,0),1),"")

I read the arbitrary lookups section on Pearson's site
(http://www.cpearson.com/excel/lookups.htm), but don't know how to
modify the formulas to fit my situation. Can anyone please help?