Help needed with function
 

I have a large table created on a worksheet called 'Times' where I
have sorted people into squads to compete in a competitition. They
compete a few times during the day on different ranges and this list
has the times under the squad number and next to the time is the range
they are competing on. The problem is the time and range number are
within a particular range but in no particular order. I am now
pulling this onto a new page called squads, and under the squad number
I am listing the times in time order and then putting the range number
next to this time. There are 111 squads and some could have no value
in them so the function establishes a range to look within once the
match has been found to the squad number which in the calculation
below is in cell G6. I have used the following formula to pull in the
time successfully but what do I do to pick up the number which will be
next to the time for that squad. The formula in each row goes to small
2, small 3 etc.
=SMALL(INDIRECT("Times!"&ADDRESS(5,MATCH(G6,Times! $AH$4:$IU
$4)+33)&":"&ADDRESS(34,MATCH(G6,Times!$AH$4:$IU$4) +33)),1)

Hope you understand this.

Help needed with function
 

Hi,

Not quite clear about the problem. May I request you to mail me the file at


--
Regards,

Ashsih Mathur
Microsoft Excel MVP
www.ashishmathur.com

wrote in message
...
I have a large table created on a worksheet called 'Times' where I
have sorted people into squads to compete in a competitition. They
compete a few times during the day on different ranges and this list
has the times under the squad number and next to the time is the range
they are competing on. The problem is the time and range number are
within a particular range but in no particular order. I am now
pulling this onto a new page called squads, and under the squad number
I am listing the times in time order and then putting the range number
next to this time. There are 111 squads and some could have no value
in them so the function establishes a range to look within once the
match has been found to the squad number which in the calculation
below is in cell G6. I have used the following formula to pull in the
time successfully but what do I do to pick up the number which will be
next to the time for that squad. The formula in each row goes to small
2, small 3 etc.
=SMALL(INDIRECT("Times!"&ADDRESS(5,MATCH(G6,Times! $AH$4:$IU
$4)+33)&":"&ADDRESS(34,MATCH(G6,Times!$AH$4:$IU$4) +33)),1)

Hope you understand this.