Error in returning LARGE values when using VLOOKUP & CHOOSE to look to the left

[Rusty315]

Hi all

Please forgive the long title, I struggled to explain this concisely.

I have a table of KPIs with names in column C and results in the columns after that. To find the top 3 achievers in the KPI in column N, I'm using this formula (basically, VLOOKUP to the left):

=VLOOKUP(LARGE(Data!$N$4:$N$35,2),CHOOSE({1,2},Data!$N$4:$N$35,Data!$C$4:$C$35),2,0)

I change the formula to look for the second and third largest values in column N as well.

It works fine for the largest value in column N if it only occurs once but if the largest value occurs twice, then the returned value for both is the same.

For example, if this were my table...

[TABLE="width: 500"]
<tbody>[TR]
[TD]Jim[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]Bob[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]Al[/TD]
[TD]80[/TD]
[/TR]
[TR]
[TD]Sam[/TD]
[TD]70[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]60[/TD]
[/TR]
</tbody>[/TABLE]

...then the result for the largest value will be Jim. The result for the second largest value will also be Jim and the third largest will be Al. Bob's name is not returned at all.

Is there a way for me to get both Jim and Bob's names returned as being the two highest and Al's name as the third?

My apologies if I haven't explained this clearly. I'm probably punching a little above my weight trying to do this using a simple formula instead of a slew of sorting functions but I'm hoping there's a simple way to do this.

Thanks for any help you can provide!!!

 

[erik.van.geit]

Hi,

See if this idea gets you started.
I changed the example: not sorted and more duplicates, so you can see what happens.

  M    N   O  
2 Jim  20  5  
3 Bob  100 1  
4 Al   80  3  
5 Sam  100 1  
6 John 80  3

RANGE FORMULA (1st cell)
O2:O6 =RANK(N2,$N$2:$N$6)+COUNTIF($B$1:B1,N2)
[Table-It] version 09 by Erik Van Geit

kind regards,
Erik

 

[Rusty315]

Hi Eric, thanks for your help, it's much appreciated!

 

[erik.van.geit]

You are most welcome.
Have a nice day!