Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup function returns duplicate values
It's been a long time since I wrote any formulas, and it is showing.
I have a stat sheet for my basketball players, I am trying to pick the top 3 in a certain category. Everything goes great, until there are duplicate values that are associated with different player names. I need to have it not only sort the top three values, but return the exact three names that go with each value (which in some cases is the same value). Last FT % Harriss 66.67% Harriss Wiger 50.00% Wiger Shuck 50.00% Shuck Ostergard 66.67% Ostergard McCann 0.00% McCann Usselman 66.67% Usselman Bryant 0.00% Bryant Chamberlain 42.31% Chamberlain Fletcher 50.00% Fletcher Liebelt 60.00% Liebelt Hamilton 25.00% Hamilton Maney 33.33% Maney Voorhees 37.50% Voorhees Player 0.00% Player Player FT % Harriss 66.67% Harriss 66.67% Harriss 66.67% Formula for finding the FT% is: =LARGE(B2:B15,1) =LARGE(B2:B16,2) =LARGE(B2:B17,3) Formula for finding the Name associated with the FT% value is: =VLOOKUP(B19,B1:C15,2,0) =VLOOKUP(B20,B1:C16,2,0) =VLOOKUP(B21,B1:C17,2,0) Now I understand why it is returning the same value, because of the simple logic involved, however I am looking for a condition to write to add to this so that it picks the next name involved and appropriately returns it. I hope this made sense. -- Appreciate your time, Ron |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup function returns duplicate values
Is there a "sort function" that can be nested into the vlookup formula?
-- Appreciate your time, Ron "Bigbelt" wrote: It's been a long time since I wrote any formulas, and it is showing. I have a stat sheet for my basketball players, I am trying to pick the top 3 in a certain category. Everything goes great, until there are duplicate values that are associated with different player names. I need to have it not only sort the top three values, but return the exact three names that go with each value (which in some cases is the same value). Last FT % Harriss 66.67% Harriss Wiger 50.00% Wiger Shuck 50.00% Shuck Ostergard 66.67% Ostergard McCann 0.00% McCann Usselman 66.67% Usselman Bryant 0.00% Bryant Chamberlain 42.31% Chamberlain Fletcher 50.00% Fletcher Liebelt 60.00% Liebelt Hamilton 25.00% Hamilton Maney 33.33% Maney Voorhees 37.50% Voorhees Player 0.00% Player Player FT % Harriss 66.67% Harriss 66.67% Harriss 66.67% Formula for finding the FT% is: =LARGE(B2:B15,1) =LARGE(B2:B16,2) =LARGE(B2:B17,3) Formula for finding the Name associated with the FT% value is: =VLOOKUP(B19,B1:C15,2,0) =VLOOKUP(B20,B1:C16,2,0) =VLOOKUP(B21,B1:C17,2,0) Now I understand why it is returning the same value, because of the simple logic involved, however I am looking for a condition to write to add to this so that it picks the next name involved and appropriately returns it. I hope this made sense. -- Appreciate your time, Ron |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup function returns duplicate values
"Bigbelt" wrote: Is there a "sort function" that can be nested into the vlookup formula? -- Appreciate your time, Ron "Bigbelt" wrote: It's been a long time since I wrote any formulas, and it is showing. I have a stat sheet for my basketball players, I am trying to pick the top 3 in a certain category. Everything goes great, until there are duplicate values that are associated with different player names. I need to have it not only sort the top three values, but return the exact three names that go with each value (which in some cases is the same value). Last FT % Harriss 66.67% Harriss Wiger 50.00% Wiger Shuck 50.00% Shuck Ostergard 66.67% Ostergard McCann 0.00% McCann Usselman 66.67% Usselman Bryant 0.00% Bryant Chamberlain 42.31% Chamberlain Fletcher 50.00% Fletcher Liebelt 60.00% Liebelt Hamilton 25.00% Hamilton Maney 33.33% Maney Voorhees 37.50% Voorhees Player 0.00% Player Player FT % Harriss 66.67% Harriss 66.67% Harriss 66.67% Formula for finding the FT% is: =LARGE(B2:B15,1) =LARGE(B2:B16,2) =LARGE(B2:B17,3) Formula for finding the Name associated with the FT% value is: =VLOOKUP(B19,B1:C15,2,0) =VLOOKUP(B20,B1:C16,2,0) =VLOOKUP(B21,B1:C17,2,0) Now I understand why it is returning the same value, because of the simple logic involved, however I am looking for a condition to write to add to this so that it picks the next name involved and appropriately returns it. I hope this made sense. -- Appreciate your time, Ron hi ron, instead of using "large" i think you should insert a column to rank your data rank(b2, $b$2:$b17) then use the resulting rank value to vlookup the name and the percentage. hope this help. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup function returns duplicate values
Appreciate the idea, tried it - but it still returns the duplicates as being
first (#1). Might work, but needs some more arguments to still rank them with no duplicates. -- Appreciate your time, Ron "RaulDR" wrote: "Bigbelt" wrote: Is there a "sort function" that can be nested into the vlookup formula? -- Appreciate your time, Ron "Bigbelt" wrote: It's been a long time since I wrote any formulas, and it is showing. I have a stat sheet for my basketball players, I am trying to pick the top 3 in a certain category. Everything goes great, until there are duplicate values that are associated with different player names. I need to have it not only sort the top three values, but return the exact three names that go with each value (which in some cases is the same value). Last FT % Harriss 66.67% Harriss Wiger 50.00% Wiger Shuck 50.00% Shuck Ostergard 66.67% Ostergard McCann 0.00% McCann Usselman 66.67% Usselman Bryant 0.00% Bryant Chamberlain 42.31% Chamberlain Fletcher 50.00% Fletcher Liebelt 60.00% Liebelt Hamilton 25.00% Hamilton Maney 33.33% Maney Voorhees 37.50% Voorhees Player 0.00% Player Player FT % Harriss 66.67% Harriss 66.67% Harriss 66.67% Formula for finding the FT% is: =LARGE(B2:B15,1) =LARGE(B2:B16,2) =LARGE(B2:B17,3) Formula for finding the Name associated with the FT% value is: =VLOOKUP(B19,B1:C15,2,0) =VLOOKUP(B20,B1:C16,2,0) =VLOOKUP(B21,B1:C17,2,0) Now I understand why it is returning the same value, because of the simple logic involved, however I am looking for a condition to write to add to this so that it picks the next name involved and appropriately returns it. I hope this made sense. -- Appreciate your time, Ron hi ron, instead of using "large" i think you should insert a column to rank your data rank(b2, $b$2:$b17) then use the resulting rank value to vlookup the name and the percentage. hope this help. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup function returns duplicate values
hi ron,
consider the following example Stat Rank with tie helper column to break the tie Final rank 66.7 1 2 2 23.5 4 1 4 33.7 3 1 3 66.7 1 1 1 using the formula rank with tie =RANK(A2,$A$2:$A$5) =RANK(A3,$A$2:$A$5) =RANK(A4,$A$2:$A$5) =RANK(A5,$A$2:$A$5) helper column =COUNTIF(B2:$B$5,$B2) =COUNTIF(B3:$B$5,$B3) =COUNTIF(B4:$B$5,$B4) =COUNTIF(B5:$B$5,$B5) final rank =IF(C21,B2+1,B2) =IF(C31,B3+1,B3) =IF(C41,B4+1,B4) =IF(C51,B5+1,B5) hope this helps. "Bigbelt" wrote: Appreciate the idea, tried it - but it still returns the duplicates as being first (#1). Might work, but needs some more arguments to still rank them with no duplicates. -- Appreciate your time, Ron "RaulDR" wrote: "Bigbelt" wrote: Is there a "sort function" that can be nested into the vlookup formula? -- Appreciate your time, Ron "Bigbelt" wrote: It's been a long time since I wrote any formulas, and it is showing. I have a stat sheet for my basketball players, I am trying to pick the top 3 in a certain category. Everything goes great, until there are duplicate values that are associated with different player names. I need to have it not only sort the top three values, but return the exact three names that go with each value (which in some cases is the same value). Last FT % Harriss 66.67% Harriss Wiger 50.00% Wiger Shuck 50.00% Shuck Ostergard 66.67% Ostergard McCann 0.00% McCann Usselman 66.67% Usselman Bryant 0.00% Bryant Chamberlain 42.31% Chamberlain Fletcher 50.00% Fletcher Liebelt 60.00% Liebelt Hamilton 25.00% Hamilton Maney 33.33% Maney Voorhees 37.50% Voorhees Player 0.00% Player Player FT % Harriss 66.67% Harriss 66.67% Harriss 66.67% Formula for finding the FT% is: =LARGE(B2:B15,1) =LARGE(B2:B16,2) =LARGE(B2:B17,3) Formula for finding the Name associated with the FT% value is: =VLOOKUP(B19,B1:C15,2,0) =VLOOKUP(B20,B1:C16,2,0) =VLOOKUP(B21,B1:C17,2,0) Now I understand why it is returning the same value, because of the simple logic involved, however I am looking for a condition to write to add to this so that it picks the next name involved and appropriately returns it. I hope this made sense. -- Appreciate your time, Ron hi ron, instead of using "large" i think you should insert a column to rank your data rank(b2, $b$2:$b17) then use the resulting rank value to vlookup the name and the percentage. hope this help. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup function returns duplicate values
Well this worked for two duplicate numbers, but did not work when there were
three that were duplicated. I just tried it and it returned two ranked as 2. If you add one more 66.7 to your list you will also see it does the same thing. So it worked for duplicates, but not triplicates and beyond. Any other ideas? Appreciate you giving me possibilities. -- Appreciate your time, Ron "RaulDR" wrote: hi ron, consider the following example Stat Rank with tie helper column to break the tie Final rank 66.7 1 2 2 23.5 4 1 4 33.7 3 1 3 66.7 1 1 1 using the formula rank with tie =RANK(A2,$A$2:$A$5) =RANK(A3,$A$2:$A$5) =RANK(A4,$A$2:$A$5) =RANK(A5,$A$2:$A$5) helper column =COUNTIF(B2:$B$5,$B2) =COUNTIF(B3:$B$5,$B3) =COUNTIF(B4:$B$5,$B4) =COUNTIF(B5:$B$5,$B5) final rank =IF(C21,B2+1,B2) =IF(C31,B3+1,B3) =IF(C41,B4+1,B4) =IF(C51,B5+1,B5) hope this helps. "Bigbelt" wrote: Appreciate the idea, tried it - but it still returns the duplicates as being first (#1). Might work, but needs some more arguments to still rank them with no duplicates. -- Appreciate your time, Ron "RaulDR" wrote: "Bigbelt" wrote: Is there a "sort function" that can be nested into the vlookup formula? -- Appreciate your time, Ron "Bigbelt" wrote: It's been a long time since I wrote any formulas, and it is showing. I have a stat sheet for my basketball players, I am trying to pick the top 3 in a certain category. Everything goes great, until there are duplicate values that are associated with different player names. I need to have it not only sort the top three values, but return the exact three names that go with each value (which in some cases is the same value). Last FT % Harriss 66.67% Harriss Wiger 50.00% Wiger Shuck 50.00% Shuck Ostergard 66.67% Ostergard McCann 0.00% McCann Usselman 66.67% Usselman Bryant 0.00% Bryant Chamberlain 42.31% Chamberlain Fletcher 50.00% Fletcher Liebelt 60.00% Liebelt Hamilton 25.00% Hamilton Maney 33.33% Maney Voorhees 37.50% Voorhees Player 0.00% Player Player FT % Harriss 66.67% Harriss 66.67% Harriss 66.67% Formula for finding the FT% is: =LARGE(B2:B15,1) =LARGE(B2:B16,2) =LARGE(B2:B17,3) Formula for finding the Name associated with the FT% value is: =VLOOKUP(B19,B1:C15,2,0) =VLOOKUP(B20,B1:C16,2,0) =VLOOKUP(B21,B1:C17,2,0) Now I understand why it is returning the same value, because of the simple logic involved, however I am looking for a condition to write to add to this so that it picks the next name involved and appropriately returns it. I hope this made sense. -- Appreciate your time, Ron hi ron, instead of using "large" i think you should insert a column to rank your data rank(b2, $b$2:$b17) then use the resulting rank value to vlookup the name and the percentage. hope this help. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Vlookup for multiple duplicate numerical values | Excel Worksheet Functions | |||
VLOOKUP returns unwanted/wrong values | Excel Worksheet Functions | |||
Array function that returns values within several intervals | Excel Worksheet Functions | |||
How do I write a VLOOKUP function that returns 0's, not neg vals? | Excel Worksheet Functions | |||
VLOOKUP with duplicate returns | Excel Discussion (Misc queries) |