{=LARGE(A1:A10,{1;2;3})}

Select 3 cells with your mouse, then enter the formula as an array (Push Ctrl, Shift and Enter simultaneously) Add more cells for more numbers.

or

{=LARGE(A1:A10,ROW(INDIRECT("1:3")))}

Select 3 cells with your mouse, then enter the formula as an array (Push Ctrl, Shift and Enter simultaneously) Add more cells for more numbers.

Zoltan Till of Hungary (http://www.hatekonysag.hu/) showed me this tip to avoid duplicates and show blank cell if there no more values to be refered to. Please refer to my blog at http://www.andrewsexceltips.netarchives/6793967.html for more details.

In cell B1 enter =MAX(A1:A10), then in cell B2 enter =IF(COUNTIF($A$1:$A$10,"<"&B1)=0,"",LARGE($A$1:$A$10,COUNTIF($A$1:$A$10,">="&B1)+1)) and drag down.

{=LARGE(IF(A1:A10=A1,B1:B10,""),{1;2;3})}

Select 3 cells with your mouse, then enter the formula as an array (Push Ctrl, Shift and Enter simultaneously) Add more cells for more numbers.

or

{=LARGE(IF(A1:A10=A1,B1:B10,""),ROW(INDIRECT("1:3")))}

Select 3 cells with your mouse, then enter the formula as an array (Push Ctrl, Shift and Enter simultaneously) Add more cells for more numbers.

**Entire Columns**

You can work with entire columns like this.

{=LARGE(A:A,ROW(INDIRECT("1:"&COUNTA(A:A))))}

Select 3 cells with your mouse, then enter the formula as an array (Push Ctrl, Shift and Enter simultaneously) Add more cells for more numbers.

If working with criteria, the above formula must be modified like this (it won't really work with entire columns, but you can just omit the top row)

{=LARGE(IF(A2:A65536=A2,B2:B65536,""),ROW(INDIRECT("1:"&COUNTA(A:A))))}

Select 3 cells with your mouse, then enter the formula as an array (Push Ctrl, Shift and Enter simultaneously) Add more cells for more numbers.

© Copyright
andrewsexceltips.net All Rights Reserved.