VLOOKUP with LARGE

[Paul Black]

Hi Everyone,

I have Numbers 1 to 49 in Cells "M6:M54".
I have Various Values in Cells "N6:S54".
What I would like to do for Example is in Cells "N56", "N57" & "N58" is
to Find the "3" Largest Values ( from Cells "N6:N54" ) and Return which
Numbers from 1 to 49 they Represent Please.
I have Tried the Formulas ...
=VLOOKUP(LARGE(N$6:N$54,1),$M6:$S54,0)
=VLOOKUP(LARGE(N$6:N$54,2),$M6:$S54,0)
=VLOOKUP(LARGE(N$6:N$54,3),$M6:$S54,0)
.... But they give me a #Value! Error.

This is what my Data Looks like :-
Cols M N O P Q R S
1 282 0 0 0 0 0
2 234 31 0 0 0 0
3 206 63 1 0 0 0
4 213 57 4 1 0 0
5 165 99 11 0 0 0
6 139 88 30 1 0 0
7 150 106 33 2 0 0
8 110 112 36 7 0 0
9 110 116 35 9 0 0
10 94 109 45 15 0 0
11 89 95 51 14 1 0
12 78 122 64 12 0 0
13 61 103 72 15 4 1
14 40 118 70 26 7 0
15 37 102 74 31 1 0
16 45 100 95 32 5 0
17 36 101 91 36 8 0
18 31 81 95 54 6 1
19 29 81 101 48 15 1
20 24 80 104 61 17 2
21 16 76 104 63 15 2
22 14 56 102 63 22 1
23 15 52 100 74 24 4
24 5 53 90 67 39 2
25 5 34 101 84 33 5
26 6 46 88 97 37 6
27 4 34 86 110 63 9
28 4 23 68 105 45 12
29 3 25 88 78 60 16
30 3 27 62 106 73 16
31 2 13 58 135 92 23
32 1 17 60 97 94 20
33 0 10 40 99 83 30
34 0 8 37 120 115 31
35 0 4 28 82 100 48
36 0 3 32 84 105 50
37 0 1 24 74 115 54
38 1 2 21 72 125 64
39 0 1 13 69 113 72
40 0 0 20 55 123 97
41 0 1 9 45 120 101
42 0 1 3 37 105 130
43 0 1 4 24 129 153
44 0 0 2 21 101 162
45 0 0 0 13 92 183
46 0 0 0 11 91 189
47 0 0 0 3 47 263
48 0 0 0 0 27 231
49 0 0 0 0 0 273

Any Help would be Appreciated.
Thanks in Advance.
All the Best.
Paul

 

[Tom Ogilvy]

=Match(LARGE(N$6:N$54,1),$N6:$N54,0)
=Match(LARGE(N$6:N$54,2),$N6:$N54,0)
=Match(LARGE(N$6:N$54,3),$N6:$N54,0)

 

[Jim Thomlinson]

The final argument of Vlookup is a number from 1 to the number of coulmens in
the range being looked up. You have a 0 which will return a #Value.

 

[Paul Black]

Brilliant!.
Thanks Tom.

All the Best.
Paul

 

[Tom Ogilvy]

No, not exactly correct. Vlookup takes 4 arguments (the last is optional).
The 3rd argument does what you say. The fourth identifies whether an exact
match is desired.

Granted, Paul was only using 3 - so within that context . . . (but you did
say Final).

 

[Paul Black]

Just One Point.
If Two Numbers have an Identical Value, it gives Only One Number TWICE.
1 1 12 [20] 31 43 49
2 2 14 [20] 34 38 47
3 4 9 22 27 40 48
4 3 8 [19] 30 41 46
5 5 10 [19] 28 34 45
6 7 7 23 33 34 44
I Used the Top Six LARGE for EACH.

Thanks in Advance.
All the Best.
Paul

 

[Paul Black]

Tom,

Will Another Approach have to be Used to Achieve this Please.

Thanks in Advance.
All the Best.
Paul

 

[Tom Ogilvy]

I replied to this a while ago, but it must have gone astray:

will let you adapt this to actuall locations. the formula looks in column
B B11:B20 and picks out the largest number and returns the corresponding
number in A11:A20. The Row(A1) part returns the order argument to the large
function, so when you drag fill it down the column it then returns 2, 3, etc
so you get the highest n numbers.

=INDEX($A$11:$A$20,MATCH(LARGE($B$11:$B$20+ROW($B$11:$B$20)/100000,ROW(A1)),
$B$11:$B$20+ROW($B$11:$B$20)/100000,0),1)

This is an array formula an should be entered with Ctrl+shift+Enter rather
than just enter.

 

[Paul Black]

Hi Tom,

Thats Done it.

Thanks VERY Much.
All the Best.
Paul