Duplicate value causes VLOOKUP issue...

[Richth]

Column A (A1:A15) displays ranked values (from 1 to 15) from quiz results
using the formula: =RANK(P1,P115,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 D2025 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?

 

[Mike]

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)

 

[Shane Devenshire]

Hi,

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

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

If this helps please click the Yes button

Cheers,
Shane Devenshire

 

[Chip Pearson]

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)

 

[Richth]

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.

 

[Richth]

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.

 

[Ashish Mathur]

Hi,

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

http://office.microsoft.com/en-us/excel/HA012260381033.aspx

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com