Find 1st, 2nd, 3rd values from a list and return adjacent cell

[Lee Francis]

Hi,


I hope you can help.


I have a list of sales records and I would like to find the 1st, 2nd, 3rd highest values from this list and return the salespersons name.


I know that I can use INDEX&MATCH formula along with LARGE but I am struggling to get this working. It feels as though it should be so simple!!!!


The data looks soemthing like


ALAN 15

JOHN 10

LEE 22

SARA 19

RANJIT 23

BOB 12


And what I want is for a table to appear as below


1st =INDEX..........

2nd

3rd

4th


Can you help please?


Thanks Guys


Lee.

 

[amrit1186]

Hi Lee


In the column D, I have put data as follows:


D

1

2

3


The formula in column E is


=INDEX($A$1:$A$6,MATCH(LARGE($B$1:$B$6,D1),$B$1:$B$6,0))


The range A1:B6 contains the data provided by you.


Amritansh

 

[Luke M]

Note that things get a lot more complicated if ties are a possibility. While the LARGE function can tell what the 4th largest number in this sequence is (3)

5

4

3

3

1


The MATCH function will look in the same place for both the 3rd and 4th, causing a problem. For tie elimination, you can use this array formula:

=INDEX($A$1:$A$6,MATCH(LARGE($B$1:$B$6+ROW($B$1:$B$6)/1000,D1),$B$1:$B$6+ROW($B$1:$B$6)/1000,0))


Remember that array formulas need to be confirmed using Ctrl+Shift+Enter.

 

[Lee Francis]

Thanks Guys,


Thats great. Is there any way for this sheet to flag ties?


Thanks again


Lee

 

[Luke M]

Quick formula in col C:

=IF(COUNTIF(B:B,B2)>1,"TIE!","")