The formula below returns the the name associated with the lowest
value in a range. How would I tweak this formula to return the 2nd
lowest value?

INDEX(A8:A34,MATCH(MIN(G8:G34),G8:G34,0))

If your min value does not occur more than once:
=INDEX(A8:A34,MATCH(SMALL(G8:G34,2),G8:G34,))

Regards,
Bernd

Use the SMALL function, like this:

INDEX(A8:A34,MATCH(SMALL(G8:G34,2),G8:G34,0))

Change the 2 to 3, 4, 5 etc to get the next smallest.

Hope this helps.

Pete

On Oct 30, 9:00 am, Sean wrote:
The formula below returns the the name associated with the lowest
value in a range. How would I tweak this formula to return the 2nd
lowest value?

INDEX(A8:A34,MATCH(MIN(G8:G34),G8:G34,0))

This was very helpful for me however i need a little more help if possible. I
am analyzing statistics for a sport (hockey) sometimes two players could have
the same value as an example player 1 has 5 goals player 2 has 8 goals and
player 3 has 5 goals. The result of this formula takes player 1 each time as
the result. How can i tweak this formula to show all the players even if they
are tied and in order? As Player 2 - 8 player 1-5 player 3-5

"Pete_UK" wrote:

Use the SMALL function, like this:

INDEX(A8:A34,MATCH(SMALL(G8:G34,2),G8:G34,0))

Change the 2 to 3, 4, 5 etc to get the next smallest.

Hope this helps.

Pete

On Oct 30, 9:00 am, Sean wrote:
The formula below returns the the name associated with the lowest
value in a range. How would I tweak this formula to return the 2nd
lowest value?

INDEX(A8:A34,MATCH(MIN(G8:G34),G8:G34,0))

Hi Randy,

have a look at this thread from a few days ago - it covers your
scenario exactly:

http://groups.google.com/group/micro...f0d7e749fdae46

Hope this helps.

Pete

On Oct 30, 8:10 pm, Randy wrote:
This was very helpful for me however i need a little more help if possible. I
am analyzing statistics for a sport (hockey) sometimes two players could have
the same value as an example player 1 has 5 goals player 2 has 8 goals and
player 3 has 5 goals. The result of this formula takes player 1 each time as
the result. How can i tweak this formula to show all the players even if they
are tied and in order? As Player 2 - 8 player 1-5 player 3-5



"Pete_UK" wrote:
Use the SMALL function, like this:

INDEX(A8:A34,MATCH(SMALL(G8:G34,2),G8:G34,0))

Change the 2 to 3, 4, 5 etc to get the next smallest.

Hope this helps.

Pete

On Oct 30, 9:00 am, Sean wrote:
The formula below returns the the name associated with the lowest
value in a range. How would I tweak this formula to return the 2nd
lowest value?

INDEX(A8:A34,MATCH(MIN(G8:G34),G8:G34,0))- Hide quoted text -

- Show quoted text -

I'm not sure why everyone is so hung up on using formulas to
accomplish this. Why not use a PivotTable instead? It's faster and
easier, and once it's set up, all you have to do is refresh it each
time source data changes.


On Oct 30, 4:10 pm, Randy wrote:
This was very helpful for me however i need a little more help if possible. I
am analyzing statistics for a sport (hockey) sometimes two players could have
the same value as an example player 1 has 5 goals player 2 has 8 goals and
player 3 has 5 goals. The result of this formula takes player 1 each time as
the result. How can i tweak this formula to show all the players even if they
are tied and in order? As Player 2 - 8 player 1-5 player 3-5



"Pete_UK" wrote:
Use the SMALL function, like this:

INDEX(A8:A34,MATCH(SMALL(G8:G34,2),G8:G34,0))

Change the 2 to 3, 4, 5 etc to get the next smallest.

Hope this helps.

Pete

On Oct 30, 9:00 am, Sean wrote:
The formula below returns the the name associated with the lowest
value in a range. How would I tweak this formula to return the 2nd
lowest value?

INDEX(A8:A34,MATCH(MIN(G8:G34),G8:G34,0))- Hide quoted text -

- Show quoted text -

Well, Ilia, with formulae you don't have to refresh it ...

There certainly seems to have been quite a few related questions on
this just recently - perhaps now that seasons are underway the sports
fans want to track their teams.

Pete

On Oct 30, 11:38 pm, ilia wrote:
I'm not sure why everyone is so hung up on using formulas to
accomplish this. Why not use a PivotTable instead? It's faster and
easier, and once it's set up, all you have to do is refresh it each
time source data changes.

On Oct 30, 4:10 pm, Randy wrote:



This was very helpful for me however i need a little more help if possible. I
am analyzing statistics for a sport (hockey) sometimes two players could have
the same value as an example player 1 has 5 goals player 2 has 8 goals and
player 3 has 5 goals. The result of this formula takes player 1 each time as
the result. How can i tweak this formula to show all the players even if they
are tied and in order? As Player 2 - 8 player 1-5 player 3-5

"Pete_UK" wrote:
Use the SMALL function, like this:

INDEX(A8:A34,MATCH(SMALL(G8:G34,2),G8:G34,0))

Change the 2 to 3, 4, 5 etc to get the next smallest.

Hope this helps.

Pete

On Oct 30, 9:00 am, Sean wrote:
The formula below returns the the name associated with the lowest
value in a range. How would I tweak this formula to return the 2nd
lowest value?

INDEX(A8:A34,MATCH(MIN(G8:G34),G8:G34,0))- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

Maybe


=INDEX(A8:A34,MATCH(LARGE(G8:G34,2),G8:G34,0))

Mike
"Sean" wrote:

The formula below returns the the name associated with the lowest
value in a range. How would I tweak this formula to return the 2nd
lowest value?

INDEX(A8:A34,MATCH(MIN(G8:G34),G8:G34,0))

OOPS, you said 2nd smallest so substitute Small for large


"Sean" wrote:

The formula below returns the the name associated with the lowest
value in a range. How would I tweak this formula to return the 2nd
lowest value?

INDEX(A8:A34,MATCH(MIN(G8:G34),G8:G34,0))

=INDEX(A8:A34,MATCH(SMALL(G8:G34,2),G8:G34,0))

check this..

On Oct 30, 2:00 pm, Sean wrote:
The formula below returns the the name associated with the lowest
value in a range. How would I tweak this formula to return the 2nd
lowest value?

INDEX(A8:A34,MATCH(MIN(G8:G34),G8:G34,0))

Thanks everyone