Duplicate value causes VLOOKUP issue...
 

Column A (A1:A15) displays ranked values (from 1 to 15) from quiz results
using the formula: =RANK(P1,P1:P15,0) from scores in column P. Column B
(B1:B15) displays Team names.

To display results of the ranking for the top 5 teams, cells C20:C25 contain
numbers 1 - 5, and cells D20:D25 contain formula:
=VLOOKUP($C20,$A$1:$B$15,2,FALSE).

Problem: If two teams happen to tie for 1st place (for example), the VLOOKUP
in D21 will fail because there is no value equal to 2 in A1:A15 range. How
can/should I deal with that?

Duplicate value causes VLOOKUP issue...
 

Large will work maybe ?
=LARGE($A$1:$A$15,1)
=LARGE($A$1:$A$15,2)
=LARGE($A$1:$A$15,3)
=LARGE($A$1:$A$15,4)
=LARGE($A$1:$A$15,5)
"Richth" wrote:

Column A (A1:A15) displays ranked values (from 1 to 15) from quiz results
using the formula: =RANK(P1,P1:P15,0) from scores in column P. Column B
(B1:B15) displays Team names.

To display results of the ranking for the top 5 teams, cells C20:C25 contain
numbers 1 - 5, and cells D20:D25 contain formula:
=VLOOKUP($C20,$A$1:$B$15,2,FALSE).

Problem: If two teams happen to tie for 1st place (for example), the VLOOKUP
in D21 will fail because there is no value equal to 2 in A1:A15 range. How
can/should I deal with that?

Duplicate value causes VLOOKUP issue...
 

Hi,

You could massage your rank function by adding some very small number to it.
For example change it to read =RANK(P1,P1:P15,0)+ROW()*10^-10

FYI I think you need P$1:P$15 if you are going to copy this formula down.

If this helps please click the Yes button

Cheers,
Shane Devenshire

"Richth" wrote:

Column A (A1:A15) displays ranked values (from 1 to 15) from quiz results
using the formula: =RANK(P1,P1:P15,0) from scores in column P. Column B
(B1:B15) displays Team names.

To display results of the ranking for the top 5 teams, cells C20:C25 contain
numbers 1 - 5, and cells D20:D25 contain formula:
=VLOOKUP($C20,$A$1:$B$15,2,FALSE).

Problem: If two teams happen to tie for 1st place (for example), the VLOOKUP
in D21 will fail because there is no value equal to 2 in A1:A15 range. How
can/should I deal with that?

Duplicate value causes VLOOKUP issue...
 

You can use "tie-breaking" with RANK to produced a unique rank for
elements with the same value. I have details and examples at
http://www.cpearson.com/Excel/rank.htm.

Another way is to use an arbitrary lookup, which allows you to get the
first, last, or an intermediate value from a lookup. See the
"Arbitrary Lookups" section at
http://www.cpearson.com/Excel/TablesAndLookups.aspx .

Cordially,
Chip Pearson
Microsoft MVP
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)




On Fri, 28 Nov 2008 11:43:01 -0800, Richth
wrote:

Column A (A1:A15) displays ranked values (from 1 to 15) from quiz results
using the formula: =RANK(P1,P1:P15,0) from scores in column P. Column B
(B1:B15) displays Team names.

To display results of the ranking for the top 5 teams, cells C20:C25 contain
numbers 1 - 5, and cells D20:D25 contain formula:
=VLOOKUP($C20,$A$1:$B$15,2,FALSE).

Problem: If two teams happen to tie for 1st place (for example), the VLOOKUP
in D21 will fail because there is no value equal to 2 in A1:A15 range. How
can/should I deal with that?

Duplicate value causes VLOOKUP issue...
 

Mike, I coudn't get LARGE to work for me. I'll study it some more to
determine where I am going wrong. Thx for posting though.

"Mike" wrote:

Large will work maybe ?
=LARGE($A$1:$A$15,1)
=LARGE($A$1:$A$15,2)
=LARGE($A$1:$A$15,3)
=LARGE($A$1:$A$15,4)
=LARGE($A$1:$A$15,5)
"Richth" wrote:

Column A (A1:A15) displays ranked values (from 1 to 15) from quiz results
using the formula: =RANK(P1,P1:P15,0) from scores in column P. Column B
(B1:B15) displays Team names.

To display results of the ranking for the top 5 teams, cells C20:C25 contain
numbers 1 - 5, and cells D20:D25 contain formula:
=VLOOKUP($C20,$A$1:$B$15,2,FALSE).

Problem: If two teams happen to tie for 1st place (for example), the VLOOKUP
in D21 will fail because there is no value equal to 2 in A1:A15 range. How
can/should I deal with that?

Duplicate value causes VLOOKUP issue...
 

Thanks Chip and others. Very well written website there Chip. Appreciate
the source. I ended up using your Unique Ranks in Ascending Order solution
in a hidden column, then modified my VLOOKUP accordingly.

"Chip Pearson" wrote:

You can use "tie-breaking" with RANK to produced a unique rank for
elements with the same value. I have details and examples at
http://www.cpearson.com/Excel/rank.htm.

Another way is to use an arbitrary lookup, which allows you to get the
first, last, or an intermediate value from a lookup. See the
"Arbitrary Lookups" section at
http://www.cpearson.com/Excel/TablesAndLookups.aspx .

Cordially,
Chip Pearson
Microsoft MVP
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

Duplicate value causes VLOOKUP issue...
 

Hi,

You may want to check this link to review the procedure of extracting
duplicate values.

http://office.microsoft.com/en-us/ex...260381033.aspx

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Richth" wrote in message
...
Column A (A1:A15) displays ranked values (from 1 to 15) from quiz results
using the formula: =RANK(P1,P1:P15,0) from scores in column P. Column B
(B1:B15) displays Team names.

To display results of the ranking for the top 5 teams, cells C20:C25
contain
numbers 1 - 5, and cells D20:D25 contain formula:
=VLOOKUP($C20,$A$1:$B$15,2,FALSE).

Problem: If two teams happen to tie for 1st place (for example), the
VLOOKUP
in D21 will fail because there is no value equal to 2 in A1:A15 range.
How
can/should I deal with that?