Having problems with vlookup function

Question

Level 1

1 points

Having problems with vlookup function

Here is a sample of my sheet that I am having issues with. I need to get the names of the Top 5 scores. In the Top 5 column, I have used the =LARGE() function to grab the 1-5th place scores. I would like to also pair that with the Name next to the score. I know a simple sort would do the trick, however, I would prefer a formula to automate the info for me.

Everytime I create the vlookup formula, I get a formula error. My formula is as follows:

=VLOOKUP(D2,$A$1:$B$19,A1:A19,0)

The error returned is that it is finding "Anthony Cavanaugh" in value 3 where a number is expected.

Anthony Cavanaugh	46	Top 5	Name
Anthony Cristaldi	52	73
Carmen Torbus	36	64
Catherine Saks	29	57
Chris Justus	56	56
Chuck Gardner	56	56
Dan Torbus	44
Dave Staton	48
Dylan Byars	42
Glen Allen	57
Jeff Strickland	56
Jesse Andrus	12
Jon Andrus	64
Kevin Markowitz	48
Martin Jacobs	73
Shawn Craver	48
Warren Vorreyer	48
Wayne Edwards	55

Posted on May 6, 2011 8:17 AM

Reply

Answer 1

AHALEY Author

Level 1

1 points

May 6, 2011 9:25 AM in response to AHALEY

ok. I figured out my first issue (syntax) and have the results I am looking for. However, As you may see, there are some duplicate rankings. If one of my top 5 spots is shared among multiple people, is there a way to have the vlookup function provide the next person for the duplicate?

John S 56

Bob Dole 99

Betty D 56

Clinton P 56

Jane Doe 70

For the Top 5 (my result)

Bob Dole 99

Jane Doe 70

Betty D 56

What I would like is:

Top 5

Bob Dole 99

Jane Doe 70

Betty D 56

Clinton P 56

-OR-

if there are duplicates, return a result as a conditional format or something else.....

Reply

Answer 2

May 6, 2011 11:34 AM in response to AHALEY

AH,

I think you might like the RANK function to pick your top 5. It's not clear to me exactly how you want to handle ties, below or at the 5th rank, so I can't be specific. If all participants tied for best score you would need to accommodate as many winners as participants.

Jerry

Reply

Answer 3

AHALEY Author

Level 1

1 points

May 6, 2011 1:50 PM in response to Jerrold Green1

I am also using the RANK function as another route, but end up at the same place, unfortunately. Since scores and ranks change from week to week, the VLOOKUP function appears to be the best way for me in order to not have to manually sort the sheet each time scores are updated.

We are also using weekly averages to break ties, but I need to rank based on a point total as mentioned earlier, then default to weekly 4 game averages for the tie breakers. See below: (point totals, rank column hidden)

Total Points	Name
38	Adam Haley
46	Anthony Cavanaugh
52	Anthony Cristaldi
36	Carmen Torbus
29	Catherine Saks
56	Chris Justus
56	Chuck Gardner
44	Dan Torbus
48	Dave Staton
42	Dylan Byars
57	Glen Allen
56	Jeff Strickland
11	Jesse Andrus
64	Jon Andrus
48	Kevin Markowitz
73	Martin Jacobs
48	Shawn Craver
48	Warren Vorreyer
55	Wayne Edwards

Next, I have ranked these in another sheet to auto-sort for me, but come up against duplicates: (using the RANK function, then Vlookup for the name with the rank)

PLACEMENT	NAME
1	Martin Jacobs
2	Jon Andrus
3	Glen Allen
4	Chris Justus
4	Chris Justus
4	Chris Justus
7	Wayne Edwards
8	Anthony Cristaldi
9	Dave Staton
9	Dave Staton
9	Dave Staton
9	Dave Staton
13	Anthony Cavanaugh
14	Dan Torbus
15	Dylan Byars
16	Adam Haley
16	Adam Haley
17	Catherine Saks
18	Jesse Andrus

and the tiebreaker will come off this sheet, where we have weekly averages

SHARK
Week 1	Week 2	Week 3
Adam Haley	153.25
Anthony Cavanaugh	165.00
Anthony Cristaldi	173.00
Carmen Torbus	152.75
Catherine Saks	139.75
Chris Justus	183.25
Chuck Gardner	183.50
Dan Torbus	166.00
Dave Staton	167.00
Dylan Byars	164.75
Glen Allen	186.50
Jeff Strickland	174.75
Jesse Andrus	0.00
Jon Andrus	201.75
Kevin Markowitz	168.00
Martin Jacobs	209.50
Shawn Craver	167.50
Warren Vorreyer	171.75
Wayne Edwards	180.50

Just not sure how to handle the duplicates on that second sheet in order to apply these as the tie-breaker

Reply

Answer 4

May 6, 2011 2:24 PM in response to AHALEY

If you already have a scheme for tie breaking, you're set to go. My favorite tie break scheme is to handle it before ranking, not after. That way you will never have to deal with a tie and you can use either LARGE or RANK without any problem.

Of course you'll want to use LOOKUP no matter what other choices you make because, as you say, it prevents having to sort.

Here's the tie-break scheme. Your total points score is in the 1-100 range and your weekly average is in the 0 to 1000 range. Create a tie-broken TotalPoints aux. column that consists of TotalPoints + WeeklyAverage/10,000. Now two otherwise equal TotalPoints scores will be offset by the difference in their WeeklyAverage scores, divided by 10,000. Unless their weekly averages are identical, there will be no tie. If there is still a possibility of a tie, take a third factor and add it in an even smaller fraction.

Jerry

Reply

Answer 5

AHALEY Author

Level 1

1 points

May 6, 2011 2:31 PM in response to Jerrold Green1

That looks perfect!!! I'll give that a shot.

Thanks!!!

Reply

Answer 6

KOENIG Yvan

Level 8

41,807 points

May 7, 2011 2:41 AM in response to AHALEY

Hello.

As I'm not hunting points, I give my two cents in a thread flagged as responded.

In cell C1 is the formula :

=COUNTIF(B,">="&B1)+ROW()/100000

In cell D1 is the formula :

=ROW()

Select columns C & D and apply Fill Down.

In E2, the formula is :

=LARGE(B,ROW()-1)

I applied Fill Down to the entire column because I wanted to show that in this case six records are in the five best ranks.

In F2, the formula is :

=OFFSET($A$1,VLOOKUP(SMALL(C,ROW()-1),C:D,2)-1,0)

Apply fill down to fill six rows.

No need for sort, correct treatment of equalities.

Yvan KOENIG (VALLAURIS, France) 7 mai 2011 11:41:21

Reply