large & lookup function
 

hi everyone,
i have this data in sheet1
A B C D
1 10 sam a
2 9 ram b
3 8 dan c
4 7 mic d
5 7 ron e
6 6 rod f
in sheet2 i would like to make lookup for the name of 5 larrgest Numbers as
the following
VLOOKUP(large(sheet1!$C$1:$E$6,A1),sheet1!$C$1:$E$ 6,2,FALSE)
but the problem i have that when the largest 4 & 5 will not coem "mic" and
"ron" because it have same value (7) only "ron" comes so is there any way to
can use this furmula with some addition to can give the large 4th is mic and
the large 5th is ron and thank for everyone give me the solution

large & lookup function
 

I wouls add a helper Column E. In E1 enter:

=B1+ROW()/1000

and copy down as far as required then hide the new Column E.

Now use the formula:

=INDEX($C$1:$C$6,MATCH(LARGE($E$1:$E$6,A1),$E$1:$E $6,FALSE))

To replace your formula.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"need a help" <need a wrote in message
...
hi everyone,
i have this data in sheet1
A B C D
1 10 sam a
2 9 ram b
3 8 dan c
4 7 mic d
5 7 ron e
6 6 rod f
in sheet2 i would like to make lookup for the name of 5 larrgest Numbers
as
the following
VLOOKUP(large(sheet1!$C$1:$E$6,A1),sheet1!$C$1:$E$ 6,2,FALSE)
but the problem i have that when the largest 4 & 5 will not coem "mic" and
"ron" because it have same value (7) only "ron" comes so is there any way
to
can use this furmula with some addition to can give the large 4th is mic
and
the large 5th is ron and thank for everyone give me the solution