Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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) |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Vlookup issue | Excel Discussion (Misc queries) | |||
vlookup issue on Mac | Excel Discussion (Misc queries) | |||
VLOOKUP issue | Excel Discussion (Misc queries) | |||
VLOOKUP issue | Excel Worksheet Functions | |||
VLOOKUP issue | Excel Worksheet Functions |