Worksheet Rank / Lookup function
 

Hi

I have a spreadsheet which you can view he

http://www.mediafire.com/?en2ywr1tjs8

If you go to the 'Gary Results' Tab, you will see that in cells C3:C5 it is
pulling 'Rhydian Roberts' through 3 times from the 'Gary Scores' Tab.
However, this is not what I intend to happen, as I would like Rhydian Roberts
to be in cell C3, Niki Evans in cell C4 and then Hope in cell C5, as they all
scored 10; with the rest following suit.

This used to work in Excel XP but I'm now using Excel 2007 and it doesn't
seem to work (I think because of the change in the 'Rank' formula).

Is there a formula that I can enter into cells C3:C14 in the 'Gary Results'
tab that will pull through the appropriate person in the correct order?


Regards

Worksheet Rank / Lookup function
 

If you want to save your file in *.xls format I'll take a look at it. I
don't have Excel 2007 but there should be no change in how the RANK function
works.

--
Biff
Microsoft Excel MVP


"Gary T" wrote in message
...
Hi

I have a spreadsheet which you can view he

http://www.mediafire.com/?en2ywr1tjs8

If you go to the 'Gary Results' Tab, you will see that in cells C3:C5 it
is
pulling 'Rhydian Roberts' through 3 times from the 'Gary Scores' Tab.
However, this is not what I intend to happen, as I would like Rhydian
Roberts
to be in cell C3, Niki Evans in cell C4 and then Hope in cell C5, as they
all
scored 10; with the rest following suit.

This used to work in Excel XP but I'm now using Excel 2007 and it doesn't
seem to work (I think because of the change in the 'Rank' formula).

Is there a formula that I can enter into cells C3:C14 in the 'Gary
Results'
tab that will pull through the appropriate person in the correct order?


Regards

Worksheet Rank / Lookup function
 

Apologies for what may have been a bit misleading below.

It may not indeed be the rank function itself, but there is definitely
something that has changed in the interaction between rank, small, offset and
match that is causing problems with exact-match ranks.

I'm unable to upload a 97-03 version at the moment as I'm at work and the
mediafire website is restricted by our firewall. In any case, I'd like to
stick with the 2007 version as it has a lot more flexibility in terms of
conditional formatting than the 97-03 versions did.

Would someone be able to look at the 2007 version?

Regards

Gary

p.s. T Valko - thanks for your response.


"T. Valko" wrote:

If you want to save your file in *.xls format I'll take a look at it. I
don't have Excel 2007 but there should be no change in how the RANK function
works.

--
Biff
Microsoft Excel MVP


"Gary T" wrote in message
...
Hi

I have a spreadsheet which you can view he

http://www.mediafire.com/?en2ywr1tjs8

If you go to the 'Gary Results' Tab, you will see that in cells C3:C5 it
is
pulling 'Rhydian Roberts' through 3 times from the 'Gary Scores' Tab.
However, this is not what I intend to happen, as I would like Rhydian
Roberts
to be in cell C3, Niki Evans in cell C4 and then Hope in cell C5, as they
all
scored 10; with the rest following suit.

This used to work in Excel XP but I'm now using Excel 2007 and it doesn't
seem to work (I think because of the change in the 'Rank' formula).

Is there a formula that I can enter into cells C3:C14 in the 'Gary
Results'
tab that will pull through the appropriate person in the correct order?


Regards

Worksheet Rank / Lookup function
 

I don't have XL2007, so I can't look at your workbook, but I think you
are suffering from MATCH (and VLOOKUP) returning only the first
occurrence in a situation where you have multiple occurrences. I
answered a very similar problem last night, so you might find this
thread to be useful:

http://groups.google.com/group/micro...8f42514cb3082#

Hope this helps.

Pete

On Oct 26, 10:25 am, Gary T wrote:
Apologies for what may have been a bit misleading below.

It may not indeed be the rank function itself, but there is definitely
something that has changed in the interaction between rank, small, offset and
match that is causing problems with exact-match ranks.

I'm unable to upload a 97-03 version at the moment as I'm at work and the
mediafire website is restricted by our firewall. In any case, I'd like to
stick with the 2007 version as it has a lot more flexibility in terms of
conditional formatting than the 97-03 versions did.

Would someone be able to look at the 2007 version?

Regards

Gary

p.s. T Valko - thanks for your response.



"T. Valko" wrote:
If you want to save your file in *.xls format I'll take a look at it. I
don't have Excel 2007 but there should be no change in how the RANK function
works.

--
Biff
Microsoft Excel MVP

"Gary T" wrote in message
...
Hi

I have a spreadsheet which you can view he

http://www.mediafire.com/?en2ywr1tjs8

If you go to the 'Gary Results' Tab, you will see that in cells C3:C5 it
is
pulling 'Rhydian Roberts' through 3 times from the 'Gary Scores' Tab.
However, this is not what I intend to happen, as I would like Rhydian
Roberts
to be in cell C3, Niki Evans in cell C4 and then Hope in cell C5, as they
all
scored 10; with the rest following suit.

This used to work in Excel XP but I'm now using Excel 2007 and it doesn't
seem to work (I think because of the change in the 'Rank' formula).

Is there a formula that I can enter into cells C3:C14 in the 'Gary
Results'
tab that will pull through the appropriate person in the correct order?

Regards- Hide quoted text -

- Show quoted text -

Worksheet Rank / Lookup function
 

See this screencap:

http://img50.imageshack.us/img50/759...upranksfi6.jpg

The formula in F2 is an array formula. Array formulas need to be entered
using the key combination of CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"Gary T" wrote in message
...
Apologies for what may have been a bit misleading below.

It may not indeed be the rank function itself, but there is definitely
something that has changed in the interaction between rank, small, offset
and
match that is causing problems with exact-match ranks.

I'm unable to upload a 97-03 version at the moment as I'm at work and the
mediafire website is restricted by our firewall. In any case, I'd like to
stick with the 2007 version as it has a lot more flexibility in terms of
conditional formatting than the 97-03 versions did.

Would someone be able to look at the 2007 version?

Regards

Gary

p.s. T Valko - thanks for your response.


"T. Valko" wrote:

If you want to save your file in *.xls format I'll take a look at it. I
don't have Excel 2007 but there should be no change in how the RANK
function
works.

--
Biff
Microsoft Excel MVP


"Gary T" wrote in message
...
Hi

I have a spreadsheet which you can view he

http://www.mediafire.com/?en2ywr1tjs8

If you go to the 'Gary Results' Tab, you will see that in cells C3:C5
it
is
pulling 'Rhydian Roberts' through 3 times from the 'Gary Scores' Tab.
However, this is not what I intend to happen, as I would like Rhydian
Roberts
to be in cell C3, Niki Evans in cell C4 and then Hope in cell C5, as
they
all
scored 10; with the rest following suit.

This used to work in Excel XP but I'm now using Excel 2007 and it
doesn't
seem to work (I think because of the change in the 'Rank' formula).

Is there a formula that I can enter into cells C3:C14 in the 'Gary
Results'
tab that will pull through the appropriate person in the correct order?


Regards