Problem with INDEX function to return top 3 objects and sizes from a list

  • Thread starter Thread starter Roger
  • Start date Start date
R

Roger

SHEET1: Hundreds of entries in no particular order. Note: there are
other columns between these fields. This is only an example.
NAME SIZE
A 1
B 5
C 3
D 12

SHEET2: On a separate sheet, I want to display the top 3 largest Names
with their corrosponding sizes
NAME SIZE
D 12
B 5
C 3

Here's what I'm using...
=INDEX(Tables!A4:O500,MATCH(LARGE(Tables!M4:M500,1),Tables!M4:M500,0),1)
LARGE: to find the largest, 2nd largest, and 3rd largest values
MATCH: to pass a row number to INDEX
INDEX: to return the value of the field I want

The problem is when 2 or more of my top 3 objects have the same size.
If I have this…
NAME SIZE
A 1
B 12
C 3
D 12

then I get this…
NAME SIZE
B 12
B 12
C 3

I want this…
NAME SIZE
B 12
D 12
C 3

I understand why this is happening. MATCH is returning the first row
number it encounters that matches the value returned by LARGE. The
problem is that I do not know how to fix it.

By the way, this might seem advanced but it really only means I'm good
at reading the help docs.
I'm an Excel newbie so please be as specific as possible!
Thanks in advance!

Roger
 
Let A1:B5 on Sheet1 house the sample you provided:

{"NAME","SIZE";"A",1;"B",5;"C",3;"D",12}

Sheet1:

In E1 enter: RANK

In E2 enter & copy down as far as needed:

=RANK(B2,$B$2:$B$5)+COUNTIF($B$2:B2,B2)-1

Sheet2:

In A1 enter: 3 [ your top N parameter ]

In A2 enter: NAME

In B2 enter: SIZE

In A3 enter & copy across:

=IF(ROW()-ROW(A$3)+1<=$A$1,INDEX(Sheet1!A$2:A$5,MATCH(ROW()-ROW(A$3)+1,Sheet
1!$C$2:$C$5,0)),"")

Note that the ROW(A$3) refers to A$3, the cell that houses theis very
formula.

Select A3:B3 and copy down.
 
Back
Top