Array lookup

[ouija]

Hi all

I have an array of data, i want to find the 1st to 5th largest numbers
in each line but if there are 2 values of the same size i want the
first that occurs to be returned as highest and the second instance to
be returned as 2nd highest.

so far when i try this it returns the same value for both 1st and 2nd
highest using hlookup

my table looks like so;
______________________________________________________________
A B C D E F G H I J K L
1. 1 2 3 4 5 0-0 1-0 0-1 2-0 1-1 0-2 3-0
2. 0.5 0.4 0.5 0.3 0.4 0.1 0.2
3. 0.5 0.3 0.2 0.4 0.6 0.1 0.0
4. 0-0 1-0 0-1 2-0 1-1 0-2 3-0
______________________________________________________________

I want;
A2 to return 0-0 (in reference to F2),
B2 to return 0-1 (in reference to H2),
C2 to return 1-0 (in reference to G2),
D2 to return 1-1 (in reference to J2),
E2 to return 2-0 (in reference to I2)

So far i have tried using HLOOKUP to say;

=HLOOKUP(LARGE(F2:L2,1),F2:L4,3,FALSE in A2
=HLOOKUP(LARGE(F2:L2,2),F2:L4,3,FALSE in B2
=HLOOKUP(LARGE(F2:L2,3),F2:L4,3,FALSE in C2
=HLOOKUP(LARGE(F2:L2,4),F2:L4,3,FALSE in D2
=HLOOKUP(LARGE(F2:L2,5),F2:L4,3,FALSE in E2

But this returns;

0-0 0-0 1-0 1-0 2-0

When what i actually want is;

0-0 0-1 1-0 1-1 2-0

Please help, if this makes sense

Ouija

 

[Bernie Deitrick]

Ouija,

In cell A2, use the array formula (enter it using Ctrl-Shift-Enter instead
of Enter)

=INDEX($F$1:$L$1,MATCH(LARGE($F2:$L2+0.01/COLUMN($F2:$L2),COLUMN()),$F2:$L2+0.01/COLUMN($F2:$L2),FALSE))

(All on one line, so watch the line breaks introduced by the newsreaders...)

Copy it to cells B2:E2, then copy A2:E2 down the column as for as you need
it.

Note that the 0.01/COLUMN() portion is meant to act as a tie-breaker. You
may need to adjust that term to account for the actual values of your data
points. - just make sure that it adds an amount smaller than the lowest
difference between your actual points. In my formula, it adds at most
0.01/6, or about .0016......

HTH,
Bernie
MS Excel MVP