large & lookup function

  • Thread starter Thread starter need a help
  • Start date Start date
N

need a help

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
 
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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
 
Back
Top